SQL Server 未公开的存储过程.docx
《SQL Server 未公开的存储过程.docx》由会员分享，可在线阅读，更多相关《SQL Server 未公开的存储过程.docx（17页珍藏版）》请在冰点文库上搜索。
SQLServer未公开的存储过程
SQLServer2000未公开的存储过程
sp_MSget_qualified_name
The sp_MSget_qualified_name storedprocedureisusedtogetthequalifiednameforthegivenobjectid.
Syntax
sp_MSget_qualified_nameobject_id,qualified_name
where
object_id-istheobjectid.object_idisint.
qualified_name-isthequalifiednameoftheobject.qualified_name
isnvarchar（512）.
Thisistheexampletogetthequalifiednameforthe authors tablefromthe pubs database.
USEpubs
GO
declare@object_idint,@qualified_namenvarchar（512）
select@object_id=object_id（'authors'）
EXECsp_MSget_qualified_name@object_id,@qualified_nameoutput
select@qualified_name
GO
Hereistheresultsetfrommymachine:
--------------------------------------
[dbo].[authors]
sp_MSdrop_object
The sp_MSdrop_object storedprocedureisusedtodropanobject（itcanbetable,view,storedprocedureortrigger）forthegivenobjectid,objectname,andobjectowner.Ifobjectid,objectname,andobjectownerarenotspecified,thennothingwillbedropped.
Syntax
sp_MSdrop_object[object_id][,object_name][,object_owner]
where
object_id-istheobjectid.object_idisint,
withadefaultofNULL.
object_name-isthenameoftheobject.object_nameissysname,
withadefaultofNULL.
object_owner-istheobjectowner.object_ownerissysname,
withadefaultofNULL.
Thisistheexampleofdroppingthe titleauthor tablefromthe pubs database.
USEpubs
GO
declare@object_idint
select@object_id=object_id（'titleauthor'）
EXECsp_MSdrop_object@object_id
GO
sp_gettypestring
The sp_gettypestring storedprocedurereturnsthetypestringforthegiventableidandcolumnid.
Syntax
sp_gettypestringtabid,colid,typestring
where
tabid-isthetableid.tabidisint.
colid-isthecolumnid.colidisint.
typestring-isthetypestring.It'soutputparameter.
typestringisnvarchar（255）.
Thisistheexampletogetthetypestringforthecolumnnumber2inthe authors table,fromthe pubs database.
USEpubs
GO
declare@tabidint,@typestringnvarchar（255）
select@tabid=object_id（'authors'）
EXECsp_gettypestring@tabid,2,@typestringoutput
select@typestring
GO
Hereistheresultsetfrommymachine:
-------------------------------
varchar（40）
sp_MSgettools_path
The sp_MSgettools_path storedprocedurereturnsthepathtotheSQLServer2000toolsandutilities.
Syntax
sp_MSgettools_pathinstall_path
where
install_path-istheinstallationpath.It'soutputparameter.
install_pathisnvarchar（260）.
ThisistheexampletogetthepathtotheSQLServer2000toolsandutilities.
USEmaster
GO
declare@install_pathNVARCHAR（260）
EXECsp_MSgettools_path@install_pathOUTPUT
select@install_path
GO
Hereistheresultsetfrommymachine:
------------------------------------------------------------
C:
\ProgramFiles\MicrosoftSQLServer\80\Tools
sp_MScheck_uid_owns_anything
The sp_MScheck_uid_owns_anything storedprocedurereturnsthelistoftheobject,ownedbythespecifieduser.
Syntax
sp_MScheck_uid_owns_anythinguid
where
uid-istheUserID,uniqueinthisdatabase.uidissmallint.
Thisistheexampletogetthelistoftheobjects,ownedbythedatabaseowner1inthe pubs database.
USEpubs
GO
EXECsp_MScheck_uid_owns_anything1
GO
sp_columns_rowset
The sp_columns_rowset storedprocedurereturnsthecompletecolumnsdescription,includingthelength,type,name,andsoon.
Syntax
sp_columns_rowsettable_name[,table_schema][,column_name]
where
table_name-isthetablename.table_nameissysname.
table_schema-isthetableschema.table_schemaissysname,
withadefaultofNULL.
column_name-isthecolumnname.column_nameissysname,
withadefaultofNULL.
Thisistheexample:
USEpubs
GO
EXECsp_columns_rowset'authors'
GO
sp_fixindex
The sp_fixindex storedprocedurecanbeusedtofixcorruptioninasystemtablebyrecreatingtheindex.
Syntax
sp_fixindexdbname,tabname,indid
where
dbname-isthedatabasename.dbnameissysname.
tabname-isthesystemtablename.tabnameissysname.
indid-istheindexidvalue.indidisint
Note.Beforeusingthisstoredprocedurethedatabasehastobeinsingleusermode.
Seethislinkformoreinformation:
HowcanIfixacorruptioninasystemtable?
Thisistheexample:
USEpubs
GO
EXECsp_fixindexpubs,sysindexes,2
GO
sp_MSforeachdb
Sometimes,youneedtoperformthesameactionsforalldatabases.Youcancreatecursorforthispurpose,oryoucanalsousethe sp_MSforeachdb storedproceduretoaccomplishthesamegoalwithlesswork.
Forexample,youcanusethe sp_MSforeachdb storedproceduretorunaCHECKDBforallthedatabasesonyourserver:
EXECsp_MSforeachdb@command1="print'?
'DBCCCHECKDB（'?
'）"
sp_MSforeachtable
Sometimes,youneedtoperformthesameactionsforalltablesinthedatabase.Youcancreatecursorforthispurpose,oryoucanalsousethe sp_MSforeachtable storedproceduretoaccomplishthesamegoalwithlesswork.
Forexample,youcanusethe sp_MSforeachtable storedproceduretorebuildalltheindexesinadatabase:
EXECsp_MSforeachtable@command1="print'?
'DBCCDBREINDEX（'?
'）"
sp_MShelpcolumns
The sp_MShelpcolumns storedprocedurereturnsthecompleteschemaforatable,includingthelength,type,name,andwhetheracolumniscomputed.
Syntax
sp_MShelpcolumnstablename[,flags][,orderby][,flags2]
where
tablename-isthetablename.tablenameisnvarchar（517）.
flags-flagsisint,withadefaultof0.
orderby-orderbyisnvarchar（10）,withadefaultofNULL.
flags-flags2isint,withadefaultof0.
Togetthefullcolumnsdescriptionforthe authors tableinthe pubs database,run:
USEpubs
GO
EXECsp_MShelpcolumns'authors'
GO
sp_MShelpindex
The sp_MShelpindex storedprocedurereturnsinformationaboutname,status,fillfactor,indexcolumnsnames,andfilegroupsforagiventable.
Syntax
sp_MShelpindextablename[,indexname][,flags]
where
tablename-isthetablename.tablenameisnvarchar（517）.
indexname-istheindexname.indexnameisnvarchar（258）,
withadefaultofNULL.
flags-flagsisint,withadefaultofNULL.
Togettheindexesdescriptionforthe authors tableinthe pubs database,run:
USEpubs
GO
EXECsp_MShelpindex'authors'
GO
sp_MShelptype
The sp_MShelptype storedprocedurereturnsmuchusefulinformationaboutsystemdatatypesanduserdatatypes.
Syntax
sp_MShelptype[typename][,flags]
where
typename-isthetypename.typenameisnvarchar（517）,
withadefaultofNULL.
flags-flagsisnvarchar（10）,withadefaultofNULL.
Togetinformationaboutallbuilt-inanduserdefineddatatypesinthe pubs database,run:
USEpubs
GO
EXECsp_MShelptype
GO
sp_MSindexspace
The sp_MSindexspace storedprocedurereturnsthesizeinkb,oftheindexesfoundinaparticulartable.
Syntax
sp_MSindexspacetablename[,index_name]
where
tablename-isthetablename.tablenameisnvarchar（517）.
index_name-istheindexname.index_nameisnvarchar（258）,
withadefaultofNULL.
Todeterminethespaceusedbytheindexesfromthe authors tableinthe pubs database,run:
USEpubs
GO
EXECsp_MSindexspace'authors'
GO
sp_MSkilldb
The sp_MSkilldb storedproceduresetsadatabasetosuspectmodeanduses DBCCDBREPAIR tokillit.Youshouldrunthisspfromthecontextofthemasterdatabase.Useitverycarefully.
Syntax
sp_MSkilldbdbname
where
dbname-isthedatabasename.dbnameisnvarchar（258）.
Tokillthe pubs database,run:
USEmaster
GO
EXECsp_MSkilldb'pubs'
GO
sp_MStablespace
The sp_MStablespace storedprocedurereturnsthenumberofrowsinatableandthespacethetableandindexuse.
Syntax
sp_MStablespacename[,id]
where
name-isthetablename.nameisnvarchar（517）.
id-idisint,withadefaultofNULL.
Todeterminethespaceusedbythe authors tableinthe pubs database,run:
USEpubs
GO
EXECsp_MStablespace'authors'
GO
Hereistheresultsetfrommymachine:
RowsDataSpaceUsedIndexSpaceUsed
--------------------------------------
23832
sp_tempdbspace
The sp_tempdbspace storedprocedurecanbeusedtogetthetotalsizeandthespaceusedbythetempdbdatabase.Itisusedwithoutparameters.
Syntax
sp_tempdbspace
Thisistheexample:
EXECsp_tempdbspace
Hereistheresultsetfrommymachine:
database_namedatabase_sizespaceused
-----------------------------------------------------------------
tempdb9.750000.562500
sp_who2
The sp_who2 storedprocedurereturnsinformationaboutcurrentSQLServer2000usersandprocessessimilartosp_who,butitprovidesmoredetailedinformation.sp_who2returnsCPUTime,DiskIO,LastBatchandProgramNameinadditiontothedataprovidedbysp_who.
Syntax
sp_who[loginame]
where
loginame-theuser'sloginname.Ifnotspecified,theprocedure
reportsonallactiveusersofSQLServer.
Thisexamplereturnsinformationforthe'sa'login:
EXECsp_who2'sa'
sp_MSgetversion
ThisextendedstoredprocedurecanbeusedtogetthecurrentversionofMicrosoftSQLServer.TogetthecurrentSQLServerversion,run
EXECmaster..sp_MSgetversion
Note.AmorecommonwaytoretrievethecurrentSQLServerversion（thiswayprovidesmoreinformation）istousefollowingSELECTstatement:
SELECT@@version
xp_dirtree
Thisextendedstoredprocedurecanbeusedtogetalistofallthefoldersforthefoldernamedinthexp.TogetalistofallthefoldersintheC:
\MSSQL7folder,run:
EXECmaster..xp_dirtree'C:
\MSSQL7'
xp_enum_oledb_providers
Thisextendedstoredprocedur
升级会员 