SQL Server系統儲存過程和引數總結

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

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

[@more@]

儲存過程名稱	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中，微軟解決了這一問題。為了執行“下一個”命令，你必須告訴其中的一個儲存過程它將使用一個不同的替換字元而不是預設的問號。我改變了資料庫命令中的這個替換字元，因為它更為簡單。

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

< p>　　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:MSSQLData'

　　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版本中最後一次可用。這個儲存過程返回關於指定檔案的大小、日期和屬性資訊，包括建立、訪問和修改的日期和次數。

< p>　　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資料型別，它接受一條記錄的名稱。如果指定了所有的引數，那麼只有指定的行作為引數返回。