SQL Server 未公开的存储过程.docx

1、SQL Server 未公开的存储过程SQL Server 2000 未公开的存储过程sp_MSget_qualified_nameThesp_MSget_qualified_namestored procedure is used to get the qualified name for the given object id.Syntaxsp_MSget_qualified_name object_id, qualified_namewhere object_id - is the object id. object_id is int.qualified_name - is the q

2、ualified name of the object. qualified_name is nvarchar(512).This is the example to get the qualified name for theauthorstable from thepubsdatabase.USE pubsGOdeclare object_id int, qualified_name nvarchar(512)select object_id = object_id(authors)EXEC sp_MSget_qualified_name object_id, qualified_name

3、 outputselect qualified_nameGOHere is the result set from my machine:-dbo.authorssp_MSdrop_objectThesp_MSdrop_objectstored procedure is used to drop an object (it can be table, view, stored procedure or trigger) for the given object id, object name, and object owner. If object id, object name, and o

4、bject owner are not specified, then nothing will be dropped.Syntaxsp_MSdrop_object object_id ,object_name ,object_ownerwhere object_id - is the object id. object_id is int, with a default of NULL.object_name - is the name of the object. object_name is sysname, with a default of NULL.object_owner - i

5、s the object owner. object_owner is sysname, with a default of NULL.This is the example of dropping thetitleauthortable from thepubsdatabase.USE pubsGOdeclare object_id intselect object_id = object_id(titleauthor)EXEC sp_MSdrop_object object_idGOsp_gettypestringThesp_gettypestringstored procedure re

6、turns the type string for the given table id and column id.Syntaxsp_gettypestring tabid, colid, typestringwhere tabid - is the table id. tabid is int.colid - is the column id. colid is int.typestring - is the type string. Its output parameter. typestring is nvarchar(255).This is the example to get t

7、he type string for the column number 2 in theauthorstable, from thepubsdatabase.USE pubsGOdeclare tabid int, typestring nvarchar(255)select tabid = object_id(authors)EXEC sp_gettypestring tabid, 2, typestring outputselect typestringGOHere is the result set from my machine:-varchar(40)sp_MSgettools_p

8、athThesp_MSgettools_pathstored procedure returns the path to the SQL Server 2000 tools and utilities.Syntaxsp_MSgettools_path install_pathwhere install_path - is the installation path. Its output parameter. install_path is nvarchar(260).This is the example to get the path to the SQL Server 2000 tool

9、s and utilities.USE masterGOdeclare install_path NVARCHAR(260)EXEC sp_MSgettools_path install_path OUTPUTselect install_pathGOHere is the result set from my machine:-C:Program FilesMicrosoft SQL Server80Toolssp_MScheck_uid_owns_anythingThesp_MScheck_uid_owns_anythingstored procedure returns the list

10、 of the object, owned by the specified user.Syntaxsp_MScheck_uid_owns_anything uidwhere uid - is the User ID, unique in this database. uid is smallint.This is the example to get the list of the objects, owned by the database owner 1 in thepubsdatabase.USE pubsGOEXEC sp_MScheck_uid_owns_anything 1GOs

11、p_columns_rowsetThesp_columns_rowsetstored procedure returns the complete columns description, including the length, type, name, and so on.Syntaxsp_columns_rowset table_name , table_schema , column_namewhere table_name - is the table name. table_name is sysname.table_schema - is the table schema. ta

12、ble_schema is sysname, with a default of NULL.column_name - is the column name. column_name is sysname, with a default of NULL.This is the example:USE pubsGOEXEC sp_columns_rowset authorsGOsp_fixindexThesp_fixindexstored procedure can be used to fix corruption in a system table by recreating the ind

13、ex.Syntaxsp_fixindex dbname, tabname, indidwhere dbname - is the database name. dbname is sysname.tabname - is the system table name. tabname is sysname.indid - is the index id value. indid is intNote. Before using this stored procedure the database has to be in single user mode.See this link for mo

14、re information:How can I fix a corruption in a system table?This is the example:USE pubsGOEXEC sp_fixindex pubs, sysindexes, 2GOsp_MSforeachdbSometimes, you need to perform the same actions for all databases. You can create cursor for this purpose, or you can also use thesp_MSforeachdbstored procedu

15、re to accomplish the same goal with less work.For example, you can use thesp_MSforeachdbstored procedure to run a CHECKDB for all the databases on your server:EXEC sp_MSforeachdb command1=print ? DBCC CHECKDB (?)sp_MSforeachtableSometimes, you need to perform the same actions for all tables in the d

16、atabase. You can create cursor for this purpose, or you can also use thesp_MSforeachtablestored procedure to accomplish the same goal with less work.For example, you can use thesp_MSforeachtablestored procedure to rebuild all the indexes in a database:EXEC sp_MSforeachtable command1=print ? DBCC DBR

17、EINDEX (?)sp_MShelpcolumnsThesp_MShelpcolumnsstored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed.Syntaxsp_MShelpcolumns tablename , flags , orderby , flags2wheretablename - is the table name. tablename is nvarchar(517).flags -

18、flags is int, with a default of 0.orderby - orderby is nvarchar(10), with a default of NULL.flags - flags2 is int, with a default of 0.To get the full columns description for theauthorstable in thepubsdatabase, run:USE pubsGOEXEC sp_MShelpcolumns authorsGOsp_MShelpindexThesp_MShelpindexstored proced

19、ure returns information about name, status, fill factor, index columns names, and file groups for a given table.Syntaxsp_MShelpindex tablename , indexname , flagswheretablename - is the table name. tablename is nvarchar(517).indexname - is the index name. indexname is nvarchar(258), with a default o

20、f NULL.flags - flags is int, with a default of NULL.To get the indexes description for theauthorstable in thepubsdatabase, run:USE pubsGOEXEC sp_MShelpindex authorsGOsp_MShelptypeThesp_MShelptypestored procedure returns much useful information about system data types and user data types.Syntaxsp_MSh

21、elptype typename , flagswheretypename - is the type name. typename is nvarchar(517), with a default of NULL.flags - flags is nvarchar(10), with a default of NULL.To get information about all built-in and user defined data types in thepubsdatabase, run:USE pubsGOEXEC sp_MShelptypeGOsp_MSindexspaceThe

22、sp_MSindexspacestored procedure returns the size in kb, of the indexes found in a particular table.Syntaxsp_MSindexspace tablename , index_namewheretablename - is the table name. tablename is nvarchar(517).index_name - is the index name. index_name is nvarchar(258), with a default of NULL.To determi

23、ne the space used by the indexes from theauthorstable in thepubsdatabase, run:USE pubsGOEXEC sp_MSindexspace authorsGOsp_MSkilldbThesp_MSkilldbstored procedure sets a database to suspect mode and usesDBCC DBREPAIRto kill it. You should run this sp from the context of the master database. Use it very

24、 carefully.Syntaxsp_MSkilldb dbnamewhere dbname - is the database name. dbname is nvarchar(258).To kill thepubsdatabase, run:USE masterGOEXEC sp_MSkilldb pubsGOsp_MStablespaceThesp_MStablespacestored procedure returns the number of rows in a table and the space the table and index use.Syntaxsp_MStab

25、lespace name , idwherename - is the table name. name is nvarchar(517).id - id is int, with a default of NULL.To determine the space used by theauthorstable in thepubsdatabase, run:USE pubsGOEXEC sp_MStablespace authorsGOHere is the result set from my machine:Rows DataSpaceUsed IndexSpaceUsed- - -23

26、8 32sp_tempdbspaceThesp_tempdbspacestored procedure can be used to get the total size and the space used by the tempdb database. It is used without parameters.Syntaxsp_tempdbspaceThis is the example:EXEC sp_tempdbspaceHere is the result set from my machine:database_name database_size spaceused- - -t

27、empdb 9.750000 .562500sp_who2Thesp_who2stored procedure returns information about current SQL Server 2000 users and processes similar to sp_who, but it provides more detailed information. sp_who2 returns CPUTime, DiskIO, LastBatch and ProgramName in addition to the data provided by sp_who.Syntaxsp_w

28、ho loginamewhereloginame - the users login name. If not specified, the procedure reports on all active users of SQL Server.This example returns information for the sa login:EXEC sp_who2 sasp_MSgetversionThis extended stored procedure can be used to get the current version of Microsoft SQL Server. To

29、 get the current SQL Server version, runEXEC master.sp_MSgetversionNote. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:SELECT versionxp_dirtreeThis extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:MSSQL7 folder, run:EXEC master.xp_dirtree C:MSSQL7xp_enum_oledb_providersThis extended stored procedur