system stored procedures sp_msforeachtable and SP_MSFOREACHDB usage instructions _mssql

1. Introduction:
As a DBA, it is often necessary to check all databases or user tables, for example: Check the capacity of all the database, see the specified database all user table capacity, the number of records of all tables ..., we generally handle such problems are handled with cursors separately, for example: when the database retrieval efficiency is very slow, We want to check all the user tables in the database, we have to write cursors to achieve the requirements, if we use sp_msforeachtable can be very convenient to achieve the same purpose: EXEC sp_msforeachtable @command1 = "print"? ' DBCC checktable ('? ')
system stored procedures sp_msforeachtable and SP_MSFOREACHDB, are two non-public stored procedures provided by Microsoft, starting from mssql6.5. stored in the master database of SQL Server. This can be used to manage all the tables in a database or to all databases on a SQL Server, which is described in detail later.

2. Parameter Description:
@command1 nvarchar (2000),--The first SQL command to run
@replacechar nchar (1) = N '? ',--specified placeholder
@command2 nvarchar = null,--second run SQL instruction
@command3 nvarchar = null,--third run SQL instruction
@whereand nvarchar = null,--optional criteria to select a table
@precommand nvarchar = null,-the action before the instruction (similar to the action before the control is triggered)
@postcommand nvarchar = null-the action after the instruction (similar to the action after the control is triggered)

Later for sp_msforeachtable parameters, SP_MSFOREACHDB does not include parameters @whereand

3. Use examples:

--Details of each table in the data base:
exec sp_msforeachtable @command1 = "sp_spaceused '?"

--Get the number of records and capacity for each table:
EXEC sp_msforeachtable @command1 = "print"? ' ",
@command2 = "sp_spaceused '",
@command3 = "SELECT count (*) from?"

--Get all the storage space for the database:
EXEC sp_msforeachdb @command1 = "print"? ' ",
@command2 = "sp_spaceused"

--Check all the databases
EXEC sp_msforeachdb @command1 = "print"? ' ",
@command2 = "DBCC CHECKDB (?) "

--Update STATISTICS for all tables in the pubs database that start with 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! ' "

--Deletes data from all tables in the current database
Sp_msforeachtable @command1 = ' Delete from? '
Sp_msforeachtable @command1 = "TRUNCATE TABLE?"

4. Use of parameter @whereand:


@whereand parameters in the stored procedure to play a role in the constraints of the instructions, the specific wording is as follows:
@whereend, it can be written @whereand = ' and O.name in (' Table1 ', ' Table2 ',.......) '
For example: I want to update the value in Table1/table2/table3 that the note column is null
Sp_msforeachtable @command1 = ' Update? Set note= ' where is NULL ', @whereand = ' and O.name in (' Table1 ', ' Table2 ', ' Table3 ') '

5. "?" These two powerful stored procedures are created by the special usage of the stored procedures.

Here "?" , which is equivalent to the DOS command and the wildcard character that we search for files in Windows.

6. Summary


With the above analysis, we can build our own Sp_msforeachobject: (paste)
Use MASTER
Go
CREATE proc Sp_msforeachobject
@objectType Int=1,
@command1 nvarchar (2000),
@replacechar nchar (1) = N '? ',
@command2 nvarchar = null,
@command3 nvarchar = null,
@whereand nvarchar = null,
@precommand nvarchar = null,
@postcommand nvarchar = null
As
/* This proc returns one or more rows for each table (optionally, matching @where), and each table defaulting to its
Own result set * *
/* @precommand and @postcommand May is used to force a single result set via 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 ten then ' Isextendedproc '
When one then ' Isreplproc '
When 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

So let's test this out:
--Get all the scripts for the stored procedure:
EXEc sp_msforeachobject @command1 = "sp_helptext '?" ", @objectType =4
--Get all the views of the script:
EXEc sp_msforeachobject @command1 = "sp_helptext '?" ", @objectType =2
-for example, in the development process, no user is their own object OWNER, so in the real database will be changed to 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
This makes it very convenient to change each database object to dbo.