?

【推薦】(SqlServer)不公開存儲過程

sp_Msforeachtable與sp_Msforeachdb詳解

——通過知識共享樹立個人品牌。

?

?

一.簡要介紹:

系統存儲過程sp_MSforeachtable和sp_MSforeachdb,是微軟提供的兩個不公開的存儲過程。從mssql6.5開始，存放在SQL Server的MASTER數據庫中。可以用來對某個數據庫的所有表或某個SQL服務器上的所有數據庫進行管理，下面將對此進行詳細介紹。

作為數據庫管理者或開發者等經常會檢查整個數據庫或用戶表。

如:檢查整個數據庫的容量，看指定數據庫所有用戶表的容量,所有表的記錄數等等，我們一般處理這樣的問題都是通過游標來達到要求。

如果我們用sp_MSforeachtable就可以非常方便的達到相同的目的，

如：sql查詢所有用戶表的列表，詳細信息，如：記錄數，表占用大小等

?

EXEC?sp_MSforeachtable?"EXECUTE?sp_spaceused?'?'"

?

二.各參數說明:

??

@command1?nvarchar(2000),??????????--第一條運行的SQL指令
??@replacechar?nchar(1)?=?N'?',??????--指定的占位符號
??@command2?nvarchar(2000)=?null,????--第二條運行的SQL指令
??@command3?nvarchar(2000)??=?null,??--第三條運行的SQL指令
??@whereand?nvarchar(2000)??=?null,??--可選條件來選擇表
??@precommand?nvarchar(2000)=?null,??--執行指令前的操作(類似控件的觸發前的操作)
??@postcommand?nvarchar(2000)=?null??--執行指令后的操作(類似控件的觸發后的操作)

?以后為sp_MSforeachtable的參數，sp_MSforeachdb不包括參數@whereand

?我們在master數據庫里執行下面的語句可以看到兩個proc詳細的代碼

?

use?master
exec?sp_helptext?sp_MSforeachtable
exec?sp_helptext?sp_Msforeachdb

?

?

三、使用舉例:

???--統計數據庫里每個表的詳細情況：

exec?sp_MSforeachtable?@command1="sp_spaceused?'?'"
?
??--獲得每個表的記錄數和容量:
??EXEC?sp_MSforeachtable?@command1="print?'?'",
?????????????????????????@command2="sp_spaceused?'?'",
?????????????????????????@command3=?"SELECT?count(*)?FROM???"
?
??--獲得所有的數據庫的存儲空間:
??EXEC?sp_MSforeachdb?@command1="print?'?'",
??????????????????????@command2="sp_spaceused?"
?
??--檢查所有的數據庫
??EXEC?sp_MSforeachdb?@command1="print?'?'",
??????????????????????@command2="DBCC?CHECKDB?(?)?"
?
??--更新PUBS數據庫中已t開頭的所有表的統計:
??EXEC?sp_MSforeachtable???@whereand="and?name?like?'t%'",
? ? ? ? ? ? ? ? ? ? ? ? ? ?@replacechar='*',
???????????????????????????@precommand="print?'Updating?Statistics.....'?print?''",
???????????????????????????@command1="print?'*'?update?statistics?*?",
? ? ? ? ? ? ? ? ? ? ? ? ? ?@postcommand=?"print''print?'Complete?Update?Statistics!'"
?
??--刪除當前數據庫所有表中的數據
??sp_MSforeachtable?@command1='Delete?from??'
??sp_MSforeachtable?@command1?=?"TRUNCATE?TABLE??"
?
--查詢數據庫所有表的記錄總數
CREATE?TABLE?#temp?(TableName?VARCHAR?(255),?RowCnt?INT)
EXEC?sp_MSforeachtable?'INSERT?INTO?#temp?SELECT?''?'',?COUNT(*)?FROM??'
SELECT?TableName,?RowCnt?FROM?#temp?ORDER?BY?TableName
DROP?TABLE?#temp
?
--檢查數據庫里每個表或索引視圖的數據、索引及text、ntext?和image?頁的完整性
--下列語句需在單用戶模式下執行（sp_dboption?'db_name',?'single?user',?'true'）
--,將true改成false就又變成多用戶了
exec?sp_msforeachtable?"dbcc?checktable('?',repair_rebuild)"

?

4.參數@whereand的用法：

?

?@whereand參數在存儲過程中起到指令條件限制的作用，具體的寫法如下：

?@whereend,可以這么寫

?

@whereand='?AND?o.name?in?(''Table1'',''Table2'',.......)'?

?

又如：

我想更新Table1/Table2/Table3中NOTE列為NULL的值

?

sp_MSforeachtable?@command1='Update???Set?NOTE=''''?Where?NOTE?is?NULL'
? ? ? ? ? ? ? ? ? ,@whereand='?AND?o.name?in?(''Table1'',''Table2'',''Table3'')'

?

5. "?"特別說明：

"?"在存儲過程的特殊用法,造就了這兩個功能強大的存儲過程.

? "?"的作用,相當于DOS命令中和我們在WINDOWS下搜索文件時的通配符的作用。

6.小結

?有上面的分析,我們可以建立自己的sp_MSforeachObject:（注：下面的內容來源于網上。）

?USE?MASTER

GO
CREATE?proc?sp_MSforeachObject
?@objectType?int=1,
?@command1?nvarchar(2000),
?@replacechar?nchar(1)?=?N'?',
?@command2?nvarchar(2000)?=?null,
????@command3?nvarchar(2000)?=?null,
?@whereand?nvarchar(2000)?=?null,
?@precommand?nvarchar(2000)?=?null,
?@postcommand?nvarchar(2000)?=?null
as
?/*?This?proc?returns?one?or?more?rows?for?each?table?(optionally,?matching?@where),?with?each?table?defaulting?to?its
own?result?set?*/
?/*?@precommand?and?@postcommand?may?be?used?to?force?a?single?result?set?via?a?temp?table.?*/
?/*?Preprocessor?won't?replace?within?quotes?so?have?to?use?str().?*/
?declare?@mscat?nvarchar(12)
?select?@mscat?=?ltrim(str(convert(int,?0x0002)))
?if?(@precommand?is?not?null)
??exec(@precommand)
?/*?Defined??@isobject?for?save?object?type?*/
?Declare?@isobject?varchar(256)
?select?@isobject=?case?@objectType?when?1?then?'IsUserTable'
?????????when?2?then?'IsView'
?????????when?3?then?'IsTrigger'
?????????when?4?then?'IsProcedure'
?????????when?5?then?'IsDefault'??
?????????when?6?then?'IsForeignKey'
?????????when?7?then?'IsScalarFunction'
?????????when?8?then?'IsInlineFunction'
?????????when?9?then?'IsPrimaryKey'
?????????when?10?then?'IsExtendedProc'???
?????????when?11?then?'IsReplProc'
?????????when?12?then?'IsRule'
??????????????????end
?/*?Create?the?select?*/
?/*?Use?@isobject?variable?isstead?of?IsUserTable?string?*/
EXEC(N'declare?hCForEach?cursor?global?for?select?''[''?+?REPLACE(user_name(uid),?N'']'',?N'']]'')?+?'']''?+?''.''?+?''[''?+
REPLACE(object_name(id),?N'']'',?N'']]'')?+?'']''?from?dbo.sysobjects?o?'
????????+?N'?where?OBJECTPROPERTY(o.id,?N'''+@isobject+''')?=?1?'+N'?and?o.category?&?'?+?@mscat?+?N'?=?0?'
???????+?@whereand)
?declare?@retval?int
?select?@retval?=?@@error
?if?(@retval?=?0)
??exec?@retval?=?sp_MSforeach_worker?@command1,?@replacechar,?@command2,?@command3
?if?(@retval?=?0?and?@postcommand?is?not?null)
??exec(@postcommand)
?return?@retval
GO

我們來測試一下:

?

--獲得所有的存儲過程的腳本:
EXEc?sp_MSforeachObject?@command1="sp_helptext?'?'?",@objectType=4
?
--獲得所有的視圖的腳本:
EXEc?sp_MSforeachObject?@command1="sp_helptext?'?'?",@objectType=2
?
--比如在開發過程中,沒一個用戶都是自己的OBJECT?OWNER,所以在真實的數據庫時都要改為DBO:
EXEc?sp_MSforeachObject?@command1="sp_changeobjectowner?'?',?'dbo'",@objectType=1
EXEc?sp_MSforeachObject?@command1="sp_changeobjectowner?'?',?'dbo'",@objectType=2
EXEc?sp_MSforeachObject?@command1="sp_changeobjectowner?'?',?'dbo'",@objectType=3
EXEc?sp_MSforeachObject?@command1="sp_changeobjectowner?'?',?'dbo'",@objectType=4

?

總結

以上是生活随笔為你收集整理的【推荐】(SqlServer)不公开存储过程sp_Msforeachtable与sp_Msforeachdb详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。