Analysis of SQL Server a stored procedure without public
Starting from SQL Server 6.5, MS provides a very useful system stored procedure sp_msforeachtable and sp_msforeachdb; as DBA often needs to check all databases or user tables, such as checking all databases; see the capacity of all user tables for specified databases All the records of all tables ... We generally handle such problems all handled processing, such as: When the database retrieval efficiency is very slow, we want to check all the user tables of the database, we must write the cursor: Declare @tablename varchar (255) Declare @exesql varchar (4000)
Declare Table_Cursor Cursor for SELECT [Name] from sysobjects where xtype = 'u'
Open table_cursorfetch next from table_cursor @tablename
While (@@ fetch_status = 0) Begin Print @tablename Select @ exesql = 'DBCC CheckTable (' '' @ TableName '') 'EXEC (@exesql) Fetch next from table_cursor @tablenameend
Close Table_Cursordeallocate Table_Cursorgo
If we use sp_msforeachtable to achieve the same purpose: exec sp_msforeachtable @ command1 = "put '?' DBCC CheckTable ('?')" You can see that this is more simple (although it is also processed through the border in the background Below we carefully analyze this stored procedure:
Let's take a look at sp_msforeachtable detailed code: use master gosptext sp_msforeachtable
- The original code of sp_msforeachtable is below
Create Proc sp_msforeachtable @ 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) = NULLAS / * This Proc Returns One or More Rows for Each Table (optionally, matching @where, with each table defaulting to it
OWN Result set * / / / * @Precommand and @PostCommand May Be used to forward a single result set via a temp table. * /
/ * Preprocessor Won't Replace with usein quotes so have to use str (). * / Declare @mscat nvarchar (12) select @mscat = LTRIM (STR (consVert (int, 0x0002)))) IF (@precommand is not null) Exec (@PReCommand)
/ * CREATE THEECT * / EXEC (N'Declare HcForeach Cursor Global for select '' ['' replace (user_name (uid), n ''] '' '') ']' ' ' '.' '' [''
Replace (Object_name (ID), N ''] '' ', N' ']' ') ' ']' 'from dbo.sysObjects o' n 'Where ObjectProperty (O.ID, N''Susertable " ') = 1' n 'and o.category &' @mscat n '= 0' @whereand) Declare @retval int search @retval = @@ error @retval = sp_msforeach_worker @ Command1, @Replacechar, @ command2, @ Command3
IF (@retval = 0 and @postcommand is not null) EXEC (@POSTCOMMAND)
Return @retval
This system stored procedure has 7 parameters: @ command1 nvarchar (2000), - The first running T-SQL instruction @ReplaceChar nchar (1) = n '?, - Specify placeholder symbol @ Command2 nvarchar ( 2000) = NULL, - The T-SQL instruction running in the second runtime @ Command3 nvarchar (2000) = NULL, - Article 3 Run T-SQL instruction @whereand nvarchar (2000) = NULL, - optional condition To select Table @Precommand nvarchar (2000) = null, - Instructions @PostCommand nVarchar (2000) = null - instructions performed after the table
So the above statement can also be written: EXEC SP_MSFOREACHTABLE @ Command1 = "Print '?'", @ Command2 = "DBCC CheckTable ('?')"
After you understand the parameters, let us do a few listed: 1. Get the number of records and capacities of each table: EXEC sp_msforeachtable @ command1 = "print '?'", @ Command2 = "sp_spaceused '?'", @ Command3 = "SELECT COUNT (*) from?" 2. Update all tables of all tables in the Pubs database: exec sp_msforeachtable @ whereand = "and name like 't%', @replacechar = '*', @ precommand = "Print 'Updating Statistics .....' print ''", @ command1 = "print '*' update statistics *", @ poscommand = "Print''Print 'Complete Update Statistics!"
In addition to @WHEREAND, sp_msForeachDB is the same as the parameters of sp_msforeach, we can detect all databases through this stored procedure, such as: 1. Check all databases EXEC SP_MSFOREACHDB @ command1 = "print '?", @ command2 = "DBCC Checkdb (?) "
With the above analysis, we can build your own sp_MSforeachObject: USE MASTERGOCREATE 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) = nullas / * 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 forward a single result set via a temp table. * /
/ * Preprocessor Won't replace with use, quotes so have to use str (). * / Declare @mscat nvarchar (12) SELECT @mscat = LTRIM (Str (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 The 'IsprimaryKey' When 10 Then 'ISEXTENDEDPROC' WHEN 11 TEN 'IsReplProc' when 12 Then 'Isrule' End
/ * Create the select * / / / * use @isobject variable @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 search @retval = @@ error @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: 1. Get all the stored procedures scripts: exec sp_msforeachobject @ command1 = "sp_helptext '?'", @ ObjectType = 42. Get all views of the script: exec sp_msforeachobject @ command1 = "sp_helptext '? ", @ ObjectType = 23. For example, in the development process, no users are their own Object Owner, so they have to change 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 is very convenient to change each database object to dbo. Of course, there are a lot of very good features, you can go deep into the study :-)
