Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Iterate through SQL Server database objects without cursors


By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | More > Scripts

Problem

There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works?

Solution

SQL Server has a couple of undocumented system stored procedures in the master database, which allow you to loop through all or selected databases using the sp_MSforeachdb system stored procedure or loop through all or selected user tables using the sp_MSforeachtable system stored procedure.

You can even extend the functionality for views, stored procedures, etc. by using the sp_MSforeach_worker stored procedure, which is in fact used by the above two stored procedures as well.

These two system stored procedures use almost the same set of parameters (more details in the below table) and return an integer value.

Parameters Type sp_Msforeachtable sp_Msforeachdb Description
@precommand nvarchar(2000) Yes Yes This command is executed before any commands and can be used for setting up an environment for commands execution.
@command1 nvarchar(2000) Yes Yes First command to be executed against each table/database.
@command2 nvarchar(2000) Yes Yes Second command to be executed against each table/database.
@command3 nvarchar(2000) Yes Yes Third command to be executed against each table/database.
@postcommand nvarchar(2000) Yes Yes This command is executed after any other commands and can be used for cleanup process after commands execution.
@replacechar nchar(1) Yes Yes Default value is "?" which represents the database/table name. You may need to change this value if you want "?" mark to be used in your query.
@whereand nvarchar(2000) Yes No With this you can specify the filtering criteria for your table collection. For details see the script section.

Script #1 using sp_MSForEachTable

This contains a simple script to demonstrate the use of sp_MSForEachTable stored procedure. The first script lists all the tables and total number of rows in the current database whereas the second script displays the space used by each table in the current database.

--Script #1 : sp_MSForEachTable system stored procedure

--List all the tables of current database and total no rows in it
EXEC sp_MSForEachTable 'SELECT ''?'' as TableName, COUNT(1) 
as TotalRows FROM ? WITH(NOLOCK)' 

--List all the tables of current database and space used by it EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];'; 
GO

Script #2 using sp_MSForEachTable

This extends the usage of last script to use other parameters. This script creates a temporary table to hold the resultsets returned by the sp_spaceused stored procedure in the pre-execute phase. Then with @command1 it updates the statistics for all the tables and with @command2 it inserts the results to the temporary table created in the pre-execute phase. Further it narrows down the list of tables to consider, which is only tables belonging to HumanResources schema by using the @whereand parameter. Finally after execution of all these commands (during post execution) it selects records from the temporary table and then drops it.

--Script #2 : sp_MSForEachTable system stored procedure 

--Creates a temporary table to hold the resultsets
--returned by sp_spaceused and before calling it, 
--it updates the statistics for each table 
--Filter out tables of HumanResources schema only 
EXECUTE sp_MSforeachtable 
@precommand = 'CREATE TABLE ##Results 
( name nvarchar(128), 
rows char(11), 
reserved varchar(50), 
data varchar(50), 
index_size varchar(50), 
unused varchar(50) 
)', 
@command1 = 'UPDATE STATISTICS ?;', 
@command2 = 'INSERT INTO ##Results EXECUTE sp_spaceused [?];', 
@whereand = 'and schema_name(schema_id) = ''HumanResources''', 
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go 

Script #3 using sp_MSForEachTable

By default sp_MSForEachTable internally uses OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 to consider only user tables. You can change this default behavior by using @whereand parameter to consider system tables or views or stored procedures or combination of these etc. For example in Script #3, the first script uses the last script as above and considers both user tables as well as system tables. In the second script it considers only views and displays its text likewise in last script it considers only stored procedures and displays its text.

--Script #3 : sp_MSForEachTable system stored procedure

--Creates a temporary table to hold the resultsets
--returned by sp_spaceused and before calling it, 
--it updates the statistics for each table 
--Note it consider both user and system tables 
EXECUTE sp_MSforeachtable 
@precommand = 'CREATE TABLE ##Results 
( name nvarchar(128), 
rows char(11), 
reserved varchar(50), 
data varchar(50), 
index_size varchar(50), 
unused varchar(50) 
)', 
@command1 = 'UPDATE STATISTICS ?;', 
@command2 = 'INSERT INTO ##Results EXECUTE sp_spaceused [?];', 
@whereand = 'or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1', 
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go Use AdventureWorks
GO 
--Display the views' script text 
EXECUTE sp_MSforeachtable 
@command1 = 'sp_helptext [?];', 
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 
or OBJECTPROPERTY(o.id, N''IsView'') = 1' 
Go 
Use AdventureWorks
GO 
--Display the stored procedures' script text 
EXECUTE sp_MSforeachtable 
@command1 = 'sp_helptext [?];', 
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 
or OBJECTPROPERTY(o.id, N''IsProcedure'') = 1' 
Go 

Script #4 using sp_MSForEachDB

Script #4 demonstrates the usage of the sp_MSForEachDb stored procedure. The first script runs DBCC CHECKDB command against all the database to check the allocation, logical and physical structural integrity of all the objects inside a database.

The second script first excludes the system databases and takes a backup of all the user databases.

--Script #4 : sp_MSForEachDb system stored procedure

--Checks the allocation, logical and physical structural 
--integrity of all the objects of all the databases 
EXEC sp_MSForEachdb 
@command1 = 'DBCC CHECKDB([?])' 
GO --Does Backup of all the databases except system databases

DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'')' + 'BEGIN ' 
+ 'Print ''Backing up ? database...'';' 
+ 'BACKUP DATABASE [?] TO DISK=''' + 'D:\?_' + replace(convert(varchar,GETDATE(),120),':','') + '.bak''' 
+ 'END' 
EXEC sp_MSForEachdb 
@command1 = @cmd1 
GO
Next Steps


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, July 19, 2016 - 2:23:46 PM - Jerry Back To Top
Damn, I wish I could think of sonithemg smart like that!

Tuesday, December 08, 2015 - 8:28:00 AM - shivam purwar Back To Top

Below is my sql server stored procedure code with cursor .it take 32 minute to update both table.in fist table i have 68000 record and second table i have 42000 record.i created index also till it take 29 minuts .i want to improve performance of my procedure and cursor is mendatory to use. Give me solution on my email:shivampurwar92@gmail.com

 

my code

===============================

index creation

===========================================================================

create index iris on  DOHA_IRIS_tbl

(PAN,DATE_LOC_TRAN,TIME_LOC_TRAN ,RETR_REF_NO,C_ACCEP_TERM_ID,AMT_SETT )

 

create index nps on DOHA_NPS_tbl(TERMINAL_ID,CARD_NUMBER,TRANS_AMOUNT,TRAN_DATE,TRAN_TIME,RETR_REF_NO )

 

 

procedure creation=

============================================================================================

 

USE [ReconDemo]

GO

/****** Object:  StoredProcedure [dbo].[mynps]    Script Date: 12/7/2015 1:29:06 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[mynps]

as

DECLARE @ref varchar(50),

 @amt varchar(50),

 @tradate varchar(50),

 @time varchar(50),

  @npsRowId varchar(200),

   @irisRowId varchar(200),

   @termId varchar(50),

   @pan varchar(50),

   @sqlQuery nvarchar(3000),

   @updateQuery nvarchar(3000),

   @flag1 varchar(10),

    @flag2 varchar(10),

@oldstatus varchar(10),

   @newstatus varchar(10);

   

DECLARE matchingCursor CURSOR  LOCAL FAST_FORWARD FOR

SELECT RETR_REF_NO, AMT_SETT,DATE_LOC_TRAN,TIME_LOC_TRAN ,C_ACCEP_TERM_ID,pan,sys.fn_PhysLocFormatter(%%physloc%%)

 FROM DOHA_IRIS_TBL --with(index=iris) 

 where flag='0' and PROC_CODE_FIRST_2='01' and DEB_CRE_INDI ='DEBIT'

 

 

DECLARE ReversalCursor CURSOR  LOCAL FAST_FORWARD FOR

SELECT distinct sys.fn_PhysLocFormatter(%%physloc%%), RETR_REF_NO, AMT_SETT,DATE_LOC_TRAN,TIME_LOC_TRAN ,C_ACCEP_TERM_ID,pan 

 FROM DOHA_IRIS_TBL --with(index=iris)  

 where flag='0' and PROC_CODE_FIRST_2='01' and DEB_CRE_INDI ='CREDIT'

 

 

 

OPEN matchingCursor;

 

 

FETCH NEXT FROM matchingCursor

INTO @ref, @amt,@tradate,@time,@termId,@pan,@irisRowId;

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

 

 select @npsRowId=max(sys.fn_PhysLocFormatter(%%physloc%%))

from DOHA_NPS_TBL  b --WITH(index=nps) 

WHERE 

RETR_REF_NO=@ref and 

TRANS_AMOUNT=@amt and 

TRAN_DATE=@tradate and 

TRAN_TIME=@time and 

flag='0' and 

TRAN_TYPE='10' and 

b.TERMINAL_ID =@termId and

substring(b.CARD_NUMBER,0,7)=substring(@pan,0,7)and 

SUBSTRING(b.CARD_NUMBER,13,17)=substring(@pan,13,17)

 

IF @npsRowId IS not null

begin

 

--select sid from  DOHA_NPS_TBL where (sid) =(select sid from (select row_number() OVER (ORDER BY SID DESC) r,sid from DOHA_NPS_TBL ) er  where  er.r=@row)

 

  update  DOHA_NPS_TBL set flag='1' where   sys.fn_PhysLocFormatter(%%physloc%%)= @npsRowId

 

  --(sid) =(select sid from (select row_number() OVER (ORDER BY SID DESC) r,sid from DOHA_NPS_TBL where flag='0') er  where  er.r=@npsRowId)

  

  update DOHA_IRIS_tbl set flag='1' where  sys.fn_PhysLocFormatter(%%physloc%%)=@irisRowId

 

  --(sid) =(select sid from (select row_number() OVER (ORDER BY SID DESC) r,sid from DOHA_IRIS_TBL ) er  where  er.r=@irisRowId)

   -- This is executed as long as the previous fetch succeeds.

 end

   FETCH NEXT FROM matchingCursor

   INTO @ref, @amt,@tradate,@time,@termId,@pan,@irisRowId;

   

END

 

CLOSE matchingCursor;

DEALLOCATE matchingCursor;

 

 

 

-- here only reversal cursor will be start

 

OPEN ReversalCursor ;

 

 

FETCH NEXT FROM ReversalCursor

INTO @irisRowId,@ref, @amt,@tradate,@time,@termId,@pan;

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

 

 select @npsRowId=max(sys.fn_PhysLocFormatter(%%physloc%%))

from DOHA_NPS_TBL  b --WITH(index=nps)  

WHERE 

RETR_REF_NO=@ref and 

TRANS_AMOUNT=@amt and 

TRAN_DATE=@tradate and 

TRAN_TIME=@time and 

flag='0' and 

TRAN_TYPE in('99','98') and 

b.TERMINAL_ID =@termId and

substring(b.CARD_NUMBER,0,7)=substring(@pan,0,7)and 

SUBSTRING(b.CARD_NUMBER,13,17)=substring(@pan,13,17)

 

--print @npsRowId

--print @irisRowId

IF @npsRowId IS not null

begin

--print 'after if'

--print @npsRowId

--print @irisRowId

 

--select sid from  DOHA_NPS_TBL where (sid) =(select sid from (select row_number() OVER (ORDER BY SID DESC) r,sid from DOHA_NPS_TBL ) er  where  er.r=@row)

 update  DOHA_NPS_tbl set flag='2' where   sys.fn_PhysLocFormatter(%%physloc%%)= @npsRowId

 update DOHA_IRIS_tbl set flag='2' where  sys.fn_PhysLocFormatter(%%physloc%%)=@irisRowId

     -- This is executed as long as the previous fetch succeeds.

 end

   FETCH NEXT FROM ReversalCursor

   INTO @irisRowId,@ref, @amt,@tradate,@time,@termId,@pan;

   

END

 

CLOSE ReversalCursor ;

DEALLOCATE ReversalCursor ;

 

 

set @flag1='1'

set @newstatus='new'

set @flag2='2'

set @oldstatus='old'

--INSERT  into IRIS_RECONCILE_2016_TBL select * from(select IRIS_2016_tbl.* from IRIS_2016_tbl WHERE FLAG='1' AND STATUS='new')as sp

 

 

--INSERT   into IRIS_RECONCILE_REVERSAL_2016_TBL select * from (select IRIS_2016_tbl.* from IRIS_2016_tbl WHERE FLAG='2' AND STATUS='new')as sp

 

 

--INSERT   into NPS_RECONCILE_2016_TBL select * from(select NPS_2016_tbl.* from NPS_2016_tbl WHERE FLAG='1' AND STATUS='new')as sp

 

 

--INSERT   into NPS_RECONCILE_REVERSAL_2016_TBL select * from(select NPS_2016_tbl.* from NPS_2016_tbl WHERE FLAG='2' AND STATUS='new')as sp

 

 

 

 

 

 


Friday, June 14, 2013 - 2:52:59 AM - Sravani Back To Top

I Want To Write A Code For Sending A Mail To All The Members In The Table Without Using Any Loops Or Temparary Tables. Can Any One Please Help Me.....................


Wednesday, December 07, 2011 - 12:21:48 PM - Marcia Q Back To Top

I've been using these sp's for quite a while without realizing there were multiple param options.  I've only ever written a basic command.  Nice to learn more about it.  Thanks for the article.


Wednesday, December 07, 2011 - 10:43:12 AM - John Henderson Back To Top

Sean, you beat me to the punch, I was just getting ready to point out that very fact.

Arshad, would it be possible for you to update the title of this tip, as not to unknowningly mislead people who are not familiar with the undocumented stored procs. The procs are very handy when you need them, but people should understand that they are still using cursors, and the side-effect of doing so.

Perhaps the title could be "Iterate through SQL Server database objects without writing cursors"

 

Useful article, nonetheless, to inform people of these hidden gems.


Wednesday, December 07, 2011 - 9:24:39 AM - Sean Lange Back To Top

Nice article about these two very useful stored procs. I would however disagree that this approach is not using a cursor. The two MS stored procs are based solely on a cursor. They use a cursor with dynamic sql. Cursors have kind of become a poison in the sql server world, and rightly so. They are pretty horrible for performance. There are however times when a cursor is pretty much the only way to accomplish something. Executing a script against every database is one such task.


Tuesday, December 22, 2009 - 2:02:43 AM - arshad0384 Back To Top

Hi DavidB,

Thanks for your appreciation, I am glad you liked it. :)

For example on sp_MSforeach_worker, please refer to the definition of either sp_MSforeachtable or sp_MSForEachdb system stored procedure in master database. It gives a very good understanding of how it works. And also you can see the definition of sp_MSforeach_worker system stored procedure there itself.


Monday, December 21, 2009 - 11:57:41 AM - DavidB Back To Top

The sample scripts will be very useful. It is clever how you excluded the system databases.

Can you provide an example using sp_MSforeach_worker? This stored procedure was unknown to me until now.

Thanks for sharing your expertise.  

 


Learn more about SQL Server tools