Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

SP_MSForeachtable - Life Without Cursors.

By Brian Knight,

Is there such a thing as a task where you would not need a cursor? Hidden in the depths of the master database are a series of stored procedures that can replace some cursors with these one-liners. Behind the scenes, cursors are still used, but they will save you tons of development time.

Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor like below :

DECLARE @dataname varchar(255),
@dataname_header varchar(255)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
IF (@@fetch_status = 0)
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
PRINT @dataname_header
SELECT @dataname_header = RTRIM(UPPER(@dataname))
EXEC ("DBCC CHECKDB " + "(" + @dataname + ")")
CLOSE datanames_cursor
DEALLOCATE datanames_cursor

Beginning with version 6.5 of SQL Server, Microsoft provides a stored procedure called sp_MSreachfortable. Using the question mark as a place holder for all table names, the procedure will do the same as the above query in a single line. You can replace the above cursor with this :

sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"

You can issue up to three commands to the stored procedure using @command1 through @command3.

Total article views: 13957 | Views in the last 30 days: 14
Related Articles

TSQL Error using Cursor to loop through sysdatabases (Syntax Error)

Incorrect syntax near @dataname


call a cursor in a procedure

call a cursor in a procedure


cursor problem



Using Cursors Inside Stored procedure problem

creating cursor inside stored procedure gives error




advanced querying    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones