提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

一些SQLServer存储过程参数及举例

SQLServer存储 参数 过程 一些 举例
2023-06-13 09:14:27 时间

MicrosoftincludedseveralhundredstoredproceduresinthevariousversionsofMicrosoftSQLServerandithasdocumentedagoodpercentageofthem.Butmanystoredproceduresremainundocumented.SomeareusedwithintheEnterpriseManagerGUIinSQL2000andwerenotintendedtobeusedbyotherprocesses.Microsofthasslatedsomeofthesestoredprocedurestoberemoved(ortheyhavebeenremoved)fromfutureversionsofSQLServer.Whilethesestoredprocedurescanbeveryusefulandsaveyoulotsoftime,theycanbechangedatanytimeintheirfunctionortheycansimplyberemoved.

ThechartbelowshowsthatwhilemanyoftheprocedureshavebeencarriedthroughfromoneversionofMicrosoftSQLServertoanother,newstoredprocedureshavebeenintroduced,andsomehavebeenremovedfromtheinstallpackage.Most,ifnotall,oftheproceduresrequiretheusertobeamemberofthesysadminfixedserverroleinordertoexecutetheprocedures.Thestoredproceduresthatinteractwiththefilesystemalsorequirethattheuserexecutingtheprocedure(aswellasSQLServer"sserviceaccount)haveaccesstothefile/folder.

ProcedureName SQL2000 SQL2005 SQL2008 sp_executeresultset X 　 　 sp_MSforeachdb X X X sp_MSforeachtable X X X sp_readerrorlog X X X xp_create_subdir 　 X X Xp_delete_file 　 X X xp_dirtree X X X xp_fileexist X X X xp_fixeddrives X X X xp_getfiledetails X 　 　 xp_getnetname X X X xp_loginconfig X X X xp_makecab X 　 　 xp_msver X X X xp_get_mapi_profiles X X X xp_subdirs X X X xp_test_mapi_profile X X X xp_unpackcab X 　 　

sp_executeresultset

Microsoftremovedthishandylittleprocedurecalledsp_executeresultsetfromSQLServerinSQLServer2005.ItallowsyoutogeneratedynamicSQLcodeontheflybyusingaSELECTquery.Then,theresultingSQLcommandswillbeexecutedagainstthedatabase.Itpermitsyoutocreateasinglepieceofcodethatcan,inasinglestep,findthenumberofrecordsineverytableinyourdatabase(astheexampleshows).Thisisanundocumentedstoredprocedureandthereisnowayofknowingwhyitwasremoved.But,alas,thishandyutilityisgone.

execsp_execresultset"SELECT""SELECT""""""+name+"""""",
count(*)FROM""+name
fromsysobjects
wherextype=""U"""

sp_MSforeachdb/sp_MSforeachtable

Twoprocedures,sp_MSforeachdbandsp_MSforeachtable,arewrappersaroundacursor.TheyallowyoutoexecuteT-SQLcodeagainsteachdatabaseonyourSQLServerandeachtablewithinthecurrentdatabase,respectively.Youcannot,however,useansp_MSforeachtablecommandwithinansp_MSforeachdbcommandinSQL2000andprior.Thecursornamethatwasusedwithinthoseprocedureswasthesame(hCForEach)andwouldthereforereturnanerrorsayingthatthecursornamewasalreadyinuseforeachexecutionofthesp_MSforeachtable.InSQLServer2005,Microsoftresolvedthisissue.Inorderto"next"thecommand,youmusttelloneoftheproceduresitwillbeusingadifferentreplacementcharacterotherthanthedefaultquestionmark.Ichangethereplacementcharacterinthedatabasecommandbecauseit"seasier.

Printeachtablenameinthecurrentdatabase.

execsp_MSforeachtable"print""?"""

Printeachdatabaseonthecurrentserver.

execsp_MSforeachdb"print""?"""

Printeachtableonthecurrentserver.

execsp_MSforeachdb"use[@]execsp_MSforeachtable""print
""""@.?""""""","@"

sp_readerrorlog/xp_readerrorlog

Thestoredproceduresp_readerrorlogactuallycomesintwoforms.Eachworksthesame;oneissimplyawrapperforthesecond.Thewrapperstoredprocedureissp_readerrorloganditcallsxp_readerrorlog.Bothhavefourinputparameters,butonlythefirsttwoareusefultous.Thefirstparameterestablishesthefilenumberthatyouwishtoview.Thesecondisthelogtoview(1ornullforERRORLOG,2forSQLAgentLog).ThisallowsyoutoviewyourerrorlogsquicklyandeasilyinsteadofhavingtolookatthebloatedlogviewerthatnowcomeswithSQLServer2005andSQL2008.

ViewthecurrentSQLERRORLOGfile.

execsp_readerrorlog

execsp_readerrorlog0,1

ViewthePriorSQLAgentLogfile.

execsp_readerrorlog1,2

xp_create_subdir

IntroducedinSQLServer2005,thexp_create_subdirstoredprocedureisveryhandybecauseyoucanuseittocreatefoldersonSQLServer"sharddriveoronanetworksharefromwithinT-SQL.

execxp_create_subdir"c:\MSSQL\Data"

xp_delete_file

Usethexp_delete_filestoredprocedureintroducedinSQLServer2005todeletefilesfromSQLServer"sharddriveoranetworksharefromwithinT-SQL.

xp_dirtree

Thexp_dirtreeprocedureallowsyoutoviewthefoldertreeand/orfilelistbeneathafolder.Thisprocedurehasseveralparametersthatcontrolhowdeeptheproceduresearchesandwhetheritreturnsfilesandfoldersorfoldersonly.Thefirstparameterestablishesthefoldertolookin.(Recommendation:DonotrunthisprocedureagainsttherootofthedrivethatWindowsisinstalledonbecauseitwilltakesometimetogeneratethetreeandreturnthedata.)Thesecondparameterlimitsthenumberofrecursivelevelsthattheprocedurewilldigthrough.Thedefaultiszerooralllevels.Thethirdparametertellstheproceduretoincludefiles.Thedefaultiszeroorfoldersonly,avalueof1includesfilesintheresultset.Specifyingathirdvaluenotequaltozerowilladdanadditionalcolumntotheoutputcalledfilewhichisabitfieldshowingtheentryinafolderorfile.

Getthefulldirectorytree.

execxp_dirtree"d:\mssql\"

Getthefirsttwolevelsofthedirectorytree.

execxp_dirtree"d:\mssql\",2

Getthefirstthreelevelsofthedirectorytree,includingfiles.

execxp_dirtree"d:\mssql\",3,1

xp_fileexist

ThisSQLServerstoredprocedure,xp_fileexist,isusedtodetermineifafileexistsonSQLServer"sharddriveoronanetworkshare.Itisextremelyusefulinstoredproceduresthatloaddatafromflatfiles.Itallowsyoutocheckandseeifthefileexistsbeforeattemptingtoblindlyloadthefile.Theprocedurehastwoparameters.Usethefirstparametertodetermineifthefileorfolderyouwantexists.Thesecondisanoutputparameter,whichwhenspecified,returnsa1or0ifthefileexistsordoesnot.

Withouttheparameter.

execxp_fileexist"c:\importfile.csv"

Withtheparameter.

DECLARE@file_existsint
execxp_fileexist"c:\importfile.csv",@file_existsOUTPUT
SELECT@file_exists
 

xp_fixeddrives

Theprocedurexp_fixeddrivesisoneofthemostusefulprocedures.Itpresentsalistofalldrivelettersandtheamountoffreespaceeachdrivehas.Theparameterhasasingleoptionalinputparameterthatcanfiltertheresultsbydrivetype.Avalueof3willreturnallmassstoragedevices(CD-ROM,DVD,etc.);avalueof4willreturntheharddrives;whileavalueof2willreturnremovablemedia(USBthumbdrives,flashdrives,etc.).

Returnalldrives.

execxp_fixeddrives

Returnharddrivesonly.

execxp_fixeddrives2

xp_getfiledetails

Theprocedurexp_getfiledetailsisanotherextremelyusefulprocedure,whichwaslastavailableinSQLServer2000.Thisprocedurereturnssize,dateandattributeinformationaboutthefilespecified,includingdateandtimescreated,accessedandmodified.

execxp_getfiledetails"c:\filetoload.csv"

xp_getnetname

Theprocedurexp_getnetnamereturnsthenameofthephysicalmachinewhereMicrosoftSQLServerisinstalled.Youcanhavethemachinenamereturnedasarecordsetorasavariable.

Withouttheparameter.

execxp_getnetname

Usingtheparameter.

DECLARE@machinenamesysname
execxp_getnetname@machinenameOUTPUT
select@machinename
 

xp_loginconfig

ThisSQLServerstoredprocedurewilltellyousomebasicauthenticationinformationabouttheuserexecutingit.Ittellsyoutheauthenticationmethod(WindowsversusSQLLogin),thedefaultdomainoftheserver,theauditlevel,aswellassomeinternalseparatorinformation.

execxp_loginconfig

xp_makecab

BackinSQLServer2000,MicrosoftgaveustheabilitytocompressOSfilesdirectlyfromT-SQLwithouthavingtoshellouttoDOSviaxp_cmdshellandrunthird-partysoftware,likepkziporwinzip.Thatcommandwasxp_makecab.Itallowsyoutospecifyalistoffilesyouwanttocompressaswellasthecabfileyouwanttoputthemin.Itevenletsyouselectdefaultcompression,MSZIPcompression(akintothe.zipfileformat)ornocompression.Thefirstparametergivesthepathtothecabfileinwhichyouwanttocreateoraddfilesto.Thesecondparameteristhecompressionlevel.Thethirdparameterappliesifyouwanttouseverboselogging.Startingwiththefourthparameterandondownarethenamesofthefilesyouwanttocompress.Inmytesting,Iwasabletopass45filenamestobecompressedtotheextendedstoredprocedure,whichmeansthatitisaveryflexiblesolutiontoyourdatacompressionrequirements.

execxp_makecab"c:\test.cab","mszip",1,"c:\test.txt","c:\test1.txt"

xp_msver

Theprocedurexp_msverisveryusefulwhenlookingforsysteminformation.Itreturnsawealthofinformationaboutthehostoperatingsystem--theSQLversionnumber,language,CPUtype,copyrightandtrademarkinformation,MicrosoftWindowsversion,CPUcountandaffinitysettings,physicalmemorysettingsandyourproductkey.Thisprocedurehasmanyinputparametersthatallowyoutofilterdowntherecordsthatarereturned.Eachparameterisasysnamedatatype,whichacceptsthenameofoneoftherecords.Ifanyparametersarespecified,onlytherowsspecifiedasaparameterarereturned.

Nofilterspecified.

execxp_msver

ReturnonlyPlatformandCommentsrecords.

execxp_msver"Platform","Comments"

xp_get_mapi_profiles

Thexp_get_mapi_profilesprocedureassistsyouinconfiguringSQLMail.Whenexecuted,itwillcalltoWindowsviatheSQLMailcomponentofSQLServeranddisplayalistofavailableMAPIprofilesthatareconfiguredinOutlookanditspecifieswhichprofileisthedefaultprofile.Ifitdoesn"tdisplayanyrecords,theneitherOutlookisnotconfiguredcorrectlyorSQLServerisnotrunningunderadomainaccountwithOutlookprofilesconfigured.InordertousethisprocedureinSQLServer2005orSQLServer2008,youmustenablethe"SQLMailXPs"optionintheSurfaceAreaConfigurationtoolorwithinthesp_configureprocedure.

execxp_get_mapi_profiles

xp_subdirs

Thexp_subdirsproceduredisplaysasubsetoftheinformationavaialblethroughxp_dirtree.Xp_subdirswilldisplayallthesubfoldersinagivenfolder.Itcanbeveryhandywhenyouarebuildingadirectorytreewithinatabledynamicallyandyoudonotwanttoworryabouttheextraparametersofthexp_dirtreeprocedure.

execxp_subdirs"d:\mssql"

xp_test_mapi_profiles

Theprocedurexp_test_mapi_profilesisanotherundocumentedstoredprocedurethatisveryusefulwhenyouaresettingupSQLMail.Itwillstart,thenstop,aMAPIsessiontoensurethatMAPIisconfiguredcorrectlyandworkingwithintheconfinesofMicrosoftSQLServer.IshouldnotethatitdoesnotverifythemailserverconfigurationwithintheMAPIclient(Outlook)nordoesitsendatestmessage.

Theprocedureacceptsasingleinputparameter.ThatparameteristhenameoftheMAPIprofileyouwishtotest.Likethexp_get_mapi_profilesprocedure,forthisstoredproceduretofunctioninSQLServer2005andSQLServer2008,youmustenablethe"SQLMailXPs"optionintheSurfaceAreaConfigurationtoolorwithinthesp_configureprocedure.

WhenworkingwiththeSQLMailstoredprocedures,beawarethatSQLMailisstillslatedforremovalfromtheMicrosoftSQLServerplatform.Thatmeanstheproceduressp_get_mapi_profilesandxp_test_mapi_profilesareslatedforremoval,astheyarepartoftheSQLMailsubsystem.YoushoulddoallmailworkonSQLServer2005andlaterusingDatabaseMailinsteadofSQLMailtoensurecodeportabilitywithfutureversionsofSQLServer.MicrosoftinitiallyslatedSQLMailforremovalinSQLServer2008,however,basedonitsinclusioninthecurrentbetarelease,itsfutureinSQLServer2008isunknown.

xp_unpackcab

Alongwiththexp_makecabprocedurecomesthexp_unpackcabextendedstoredprocedure,anditdoesjustwhatitsays:Itextractsfilesfromcabfiles.Thefirstparamateristhecabfile,thesecondisthepathyouwanttoextracttoandthethirdisverboselogging.Afourthparamaterletsyouspecifythe"extractto"filename.

execxp_unpackcab"c:\test.cab","c:\temp\",1

WhilethisisnotintendedtobeacompletelistoftheundocumentedstoredproceduresinSQLServer,itdoesprovideareferencepointformanyoftheseprocedureswiththehopeofmakingthelivesoftheSQLServeradministratorseasier.Remember,youshouldnevercountontheseproceduressurvivingfromoneSQLServerversiontothenext,norshouldyouexpecttheircodebasetoremainthesamebetweenversions.Thatsaid,gocodeandenjoy.

AllinformationprovidedaboutMicrosoftSQLServer2008(Katmai)isbasedonbetaedition10.0.1019ofthesoftwareandissubjecttochangewithoutnotice.

相关文章