SQL Server預存程序和參數樣本

        一些用在SQL 2000的企業管理GUI中，並且不打算用於其他的流程。微軟已預計將其中的一些預存程序從未來的SQL Server版本中刪除(或已經刪除了)。雖然這些預存程序可能很有用並為你節省了很多時間，但是他們可以在任何時候改變他們的函數或簡單的刪除掉。

　　下面的圖表顯示了當許多預存程序從一個Microsoft SQL Server版本移入另一個版本時，引入了新的預存程序，而原來的一些則從安裝包裡刪除了。大多數的預存程序，如果不是所有的，要求使用者是系統管理員伺服器角色以便執行這些預存程序。和檔案系統互動的預存程序還要求執行預存程序的使用者(還有SQL Server的服務帳戶)具有訪問檔案/檔案夾的許可權。

預存程序名稱	SQL 2000	SQL 2005	SQL 2008
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

　　微軟在SQL Server 2005中刪除了這個名為sp_executeresultset的便利小程式。它允許你在空閑時通過使用SELECT查詢產生動態SQL代碼。然後，作為結果的SQL命令將會在資料庫上執行。它允許你建立單獨的一行代碼，這行代碼可以在單步中查詢到你的資料庫裡的每一個表的記錄數目(就像例子中所顯示的)。這是一個未公開的預存程序，而且無法知道它為什麼被刪除了。但是，唉，這個便利的有用預存程序已經沒有了。　　

exec sp_execresultset 'SELECT ''SELECT '''''' + name + '''''', 　　count(*) FROM '' + name 　　from sysobjects 　　where xtype = ''U''' 　　sp_MSforeachdb / sp_MSforeachtable

 

　 sp_MSforeachdb / sp_MSforeachtable 　　

　　兩個預存程序，sp_MSforeachdb和sp_MSforeachtable封裝了一個指標。它們允許你對你的SQL Server上的每一個資料庫和當前資料庫中的每一個表分別執行T-SQL代碼。然而你不能在SQL2000和之前的版本中在一個sp_MSforeachdb命令中使用sp_MSforeachtable命令。在這些預存程序中使用的指標名稱是一樣的(hCForEach)，因此在每一次執行sp_MSforeachtable時會返回一個錯誤說該指標名稱已經在使用。在SQL Server 2005中，微軟解決了這一問題。為了執行“下一個”命令，你必須告訴其中的一個預存程序它將使用一個不同的替換字元而不是預設的問號。我改變了資料庫命令中的這個替換字元，因為它更為簡單。

　　列印當前資料庫中的每一個表的名稱

　　exec sp_MSforeachtable 'print ''?'''

　　列印當前伺服器的每一個資料庫

　　exec sp_MSforeachdb 'print ''?'''

　　列印當前資料庫的每一個表

　　exec sp_MSforeachdb 'use [@] exec sp_MSforeachtable ''print

　　''''@.?''''''', '@'

　　sp_readerrorlog / xp_readerrorlog

　　預存程序sp_readerrorlog實際上有兩種形式。每一項的工作內容一樣;一個是另一個的簡單封裝。封裝的預存程序是sp_readerrorlog，它調用xp_readerrorlog。這兩個都有四個輸入參數，但是只有前兩個對我們有用。第一個參數設定你希望看到的檔案編號。第二個是要查看的日誌(對於ERRORLOG是1或空，對SQL Agent Log是2)。這允許你快速並很容易地查看你的錯誤記錄檔，而不是得查看SQL Server 2005和SQL 2008所帶來的臃腫的日誌閱覽器。

　　查看當前的SQL ERRORLOG檔案。

　　exec sp_readerrorlog

　　exec sp_readerrorlog 0, 1

　　查看之前的SQL Agent Log檔案。

　　exec sp_readerrorlog 1, 2

　　xp_create_subdir

　　在SQL Server 2005中引入的xp_create_subdir預存程序是非常輕便的，因為你可以用它在SQL Server的硬碟上或從T-SQL內部的網際網路共用上建立檔案夾。

　　exec xp_create_subdir 'c:/MSSQL/Data'

　　xp_delete_file

　　使用SQL Server 2005中引入的xp_delete_file預存程序從SQL Server的硬碟或從T-SQL內部的網際網路共用上刪除檔案。

　　xp_dirtree

　　xp_dirtree預存程序允許你查看資料夾樹狀目錄狀結構和/或一個檔案夾下的檔案清單。這個預存程序有幾個參數用來控制這個預存程序查詢深度和是返迴文件和檔案夾還是只返迴文件夾。第一個參數設定要查看的檔案夾。(建議;不要在Windows的系統硬碟根目錄上執行這個預存程序，因為產生樹和返回資料需要一些時間。)第二個參數限制了這個預存程序將會進行的遞迴級數。預設是零或所有層級。第三個參數告訴預存程序包括檔案。預設是零或只對檔案 夾，數值1代表包括結果集的檔案。定義第三個參數為不為零的數值將會增加一行到輸出的調用檔案，這個檔案是顯示進入一個檔案夾或檔案的一個小檔案。

　　獲得完整分類樹。

　　exec xp_dirtree 'd:/mssql/'

　　獲得分類樹的前兩級。

　　exec xp_dirtree 'd:/mssql/', 2

　　獲得目錄數的前三級，包括檔案。

　　exec xp_dirtree 'd:/mssql/', 3, 1

　　exec xp_dirtree 'd:/mssql/'

　　xp_fileexist

　　這個SQL Server預存程序，xp_fileexist，是用來決定一個檔案是存在於的硬碟上還是在網際網路共用上。它對於從規則文檔中下載資料的預存程序是非常有用的。它允許你在打算盲目的下載資料之前查看檔案是否存在。這個預存程序有兩個參數。用第一個參數來確定你想要的檔案或檔案夾是否存在。第二個參數是一個輸出參數，如果它被定義了，會根據檔案存在或不存在而返回1或0。

　　沒有這個參數：

　　exec xp_fileexist 'c:/importfile.csv'

　　有這個參數：

　　DECLARE @file_exists int

　　exec xp_fileexist 'c:/importfile.csv', @file_exists OUTPUT

　　SELECT @file_exists

　　xp_fixeddrives

　　xp_fixeddrives預存程序是最有用的預存程序之一。它展示了一個列表所有磁碟機名和每個磁碟機上的空閑空間大小。這個參數有一個單獨的可選輸入參數，它可以按磁碟機類型過濾結果。設定為數值3將會返回所有的大量存放裝置(CD-ROM，DVD等等);設定為數值4將會返回硬碟;而當設定為數值2時將會返回可移動的裝置(USB插拔磁碟機，快閃記憶體磁碟機等等)。

　　返回所有的磁碟機。

　　exec xp_fixeddrives

　　只返回硬碟

　　exec xp_fixeddrives 2

　　xp_getfiledetails

　　xp_getfiledetails是另一個非常有用的預存程序，在SQL Server 2000版本中最後一次可用。這個預存程序返回關於指定檔案的大小、日期和屬性資訊，包括建立、訪問和修改的日期和次數。

　　exec xp_getfiledetails 'c:/filetoload.csv'

　　xp_getnetname

　　xp_getnetname預存程序返回Microsoft SQL Server安裝所在的物理機器的名稱。你可以使機器名稱作為資料集返回或作為變數返回。

　　沒有這個參數

　　exec xp_getnetname

　　使用這個參數

　　DECLARE @machinename sysname

　　exec xp_getnetname @machinename OUTPUT

　　select @machinename

　　xp_loginconfig

　　SQL Server預存程序將會告訴你關於執行它的使用者的一些基礎的校正資訊。它告訴你校正方法(Windows或SQL登入)、伺服器的預設域、審計層級，還有一些內部分隔字元資訊。

　　exec xp_loginconfig

　　xp_makecab

　　在SQL Server 2000中，微軟給了我們直接從T-SQL壓縮系統檔案的能力，不必再到DOS下通過xp_cmdshell和運行第三方軟體，像pkzip或winzip來完成。這個命令就是xp_makecab。它允許你指定一列你想壓縮的檔案還有你想放進去的cab檔案。它甚至允許你選擇預設壓縮，MSZIP壓縮(類似於.zip檔案格式)或不壓縮。第一個參數給出到cab檔案的路徑，這是你想建立和添加檔案的地方。第二個參數是壓縮層級。如果你想使用詳細的日誌記錄就使用第三個參數。第四個參數後跟著你想壓縮的檔案的名稱。在我的測試裡，我可以在擴充預存程序裡傳45個要壓縮的檔案名稱，這意味著它是一個對你的資料壓縮要求來說非常靈活的解決方案。

　　exec xp_makecab 'c:/test.cab', 'mszip', 1, 'c:/test.txt' , 'c:/test1.txt'

　　xp_msver

　　預存程序xp_msver在查看系統資訊的時候是非常有用的。它返回關於主機作業系統的大量的資訊——SQL版本號碼、語言、CPU類型、著作權和商標資訊、Microsoft Windows版本、CPU數和親和性設定、實體記憶體設定和你的產品鍵。這個預存程序有許多輸入參數，它們允許你過濾返回的記錄。每一個參數都是一個sysname資料類型，它接受一條記錄的名稱。如果指定了所有的參數，那麼只有指定的行作為參數返回。

　　沒有指定過濾。

　　exec xp_msver

　　只返回Platform和Comments記錄。

　　exec xp_msver 'Platform', 'Comments'

　　xp_get_mapi_profiles

　　xp_get_mapi_profiles預存程序協助你配置SQL Mail。執行時，它會通過SQL Server 的SQL Mail組件調用Windows，並且顯示在Outlook中配置的可用的MAPI概要列表，並且它指定哪個概要是預設的概要。如果它不顯示任何記錄，那麼或者是Outlook沒有配置正確，或者是配置了Outlook概要但SQL Server沒有運行在一個域帳戶下。為了在SQL Server 2005或SQL Server 2008中使用這個預存程序，你必須在Surface Area Configuration(介面區配置器)工具或sp_configure預存程序中設定“SQL Mail XPs”選項為可用。

　　exec xp_get_mapi_profiles

　　xp_subdirs

　　xp_subdirs預存程序顯示通過xp_dirtree可用的資訊的子集。xp_subdirs將會顯示給定的檔案夾的所有的子檔案夾。當你在一個表裡動態地構建一個分類樹時它是很便利的，並且你不需要擔心xp_dirtree預存程序的其它參數。

　　exec xp_subdirs 'd:/mssql'

　　xp_test_mapi_profiles

　　xp_test_mapi_profiles預存程序是另一個未公開的預存程序，它在你建立SQL Mail時非常有用。它會啟動然後停止一個MAPI會話來確保MAPI配置正確並工作在Microsoft SQL Server之內。我應該指出，它不校正MAPI用戶端(Outlook)中的郵件伺服器配置，也不發送測試資訊。

　　預存程序接受一個單獨的輸入參數。這個參數是你要測試的MAPI概要的名稱。就像xp_get_mapi_profiles預存程序一樣，這個預存程序要運行在SQL Server 2005和SQL Server 2008之中，你必須在Surface Area Configuration(介面區配置器)工具或sp_configure預存程序中設定“SQL Mail XPs”選項為可用。

　　當使用SQL Mail預存程序時，要清楚SQL Mail仍預計要從Microsoft SQL Server平台中刪除。這意味著sp_get_mapi_profiles和xp_test_mapi_profiles預存程序是預計要刪除的，因為他們是SQL Mail子系統的一部分。你應該在SQL Server 2005中做所有的郵件工作，然後使用Database Mail替代SQL Mail來確保代碼可移植到SQL Server的未來版本中。微軟最初計劃在SQL Server 2008中刪除SQL Mail，但基於其包括在了當前的測試版本中，它未來是否存在於SQL Server 2008中還是未知數。

　　xp_unpackcab

　　隨xp_makecab預存程序而來的xp_unpackcab擴充預存程序，它能做的就像它的名字一樣：從cab檔案提取檔案。第一個參數是cab檔案，第二個參數是你想提取到的路徑，第三個參數是詳細記錄日誌。第四個參數使你可以指定“提取到”的檔案名稱。

　　exec xp_unpackcab 'c:/test.cab', 'c:/temp/', 1

　　雖然這並不打算羅列SQL Server中未公開的預存程序的完整列表，但它確實持著使SQL Server管理員的工作容易些的期望提供了一個對許多未公開的預存程序的參考。記住，你絕不應該指望從一個SQL Server版本到另一個版本時這些預存程序仍然存在，你也不應該期待在不同的版本間它們的代碼基礎仍然相同。意思就是，去編碼並享受它帶來的便利。

　　所有提供的關於Microsoft SQL Server 2008(Katmai)的資訊是基於該軟體的測試版10.0.1019。