Two SQL Server Stored Procedures: sp_msforeachtable/sp_msforeachdb

1. overview:
As a DBA, you often need to check all databases or user tables, such as checking the capacity of all databases and the capacity of all user tables in the specified database, number of records in all tables ..., generally, we use cursors to process such problems separately. For example, when the database retrieval efficiency is very slow, we want to check all the user tables in the database, we must write the cursor to meet the requirements. If we use sp_msforeachtable, we can easily achieve the same purpose: exec sp_msforeachtable @ command1 = "print '? 'Dbcc checktable ('? ') "
the system stored procedures sp_msforeachtable and sp_msforeachdb are two undisclosed stored procedures provided by Microsoft, starting from mssql6.5. Stored in the master database of SQL Server. It can be used to manage all tables of a database or all databases on an SQL server. This will be detailed later.

2. parameter description:
@ command1 nvarchar (2000), -- the first running SQL command
@ replacechar nchar (1) = n '? ', -- The specified placeholder symbol
@ command2 nvarchar (2000) = NULL, -- the second running SQL command
@ command3 nvarchar (2000) = NULL, -- the third running SQL command
@ whereand nvarchar (2000) = NULL, -- select a table as an optional condition
@ precommand nvarchar (2000) = NULL, -- the operation before executing the command (similar to the operation before the control is triggered)
@ postcommand nvarchar (2000) = NULL -- the operation after the command is executed (similar to the operation after the control is triggered)

It will be the sp_msforeachtable parameter later. sp_msforeachdb does not include the parameter @ whereand.

3. Example:

-- Details of each table in the statistics database:
Exec sp_msforeachtable @ command1 = "sp_spaceused '? '"

-- Obtain the number and capacity of records for each table:
Exec sp_msforeachtable @ command1 = "print '? '",
@ Command2 = "sp_spaceused '? '",
@ Command3 = "select count (*) from? "

-- Obtain the storage space of all databases:
Exec sp_msforeachdb @ command1 = "print '? '",
@ Command2 = "sp_spaceused"

-- Check all databases
Exec sp_msforeachdb @ command1 = "print '? '",
@ Command2 = "DBCC checkdb (?) "

-- Update statistics of all tables starting with "T" in the pubs database:
Exec sp_msforeachtable @ whereand = "and name like't % '",
@ Replacechar = '*',
@ Precommand = "Print 'updating statistics... 'print ''",
@ Command1 = "print '*' update statistics *",
@ Postcommand = "Print ''print 'complete update statistics! '"

-- Delete data from all tables of the current database
Sp_msforeachtable @ command1 = 'delete from? '
Sp_msforeachtable @ command1 = "truncate table? "

4. usage of the parameter @ whereand:

@ Whereand the parameter plays a role as a command condition restriction in the stored procedure. The specific statement is as follows:
@ Whereend, you can write @ whereand = 'and O. Name in (''table1', ''table2 '',.......)'
For example, I want to update the null value of the note column in Table1/Table2/table3.
Sp_msforeachtable @ command1 = 'Update? Set note = ''' where note is null', @ whereand = 'and O. name in (''table1', ''table2', ''table3 '')'

5 ."? "The special usage of stored procedures has created these two powerful stored procedures.

Here "? "Is equivalent to the wildcard in the doscommand and when we search for files in windows.

6. Summary

With the above analysis, we can create our own sp_msforeachobject: (post)
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

Let's test it as follows:
-- Obtain the scripts for all stored procedures:
Exec sp_msforeachobject @ command1 = "sp_helptext '? '", @ Objecttype = 4
-- Scripts for obtaining all views:
Exec sp_msforeachobject @ command1 = "sp_helptext '? '", @ Objecttype = 2
-- For example, in the development process, none of the users are their own object owners, so they should change to DBO in real databases:
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
This makes it easy to change each database object to DBO.