SP_MSForeachtable - Life Without Cursors.

,

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)

BEGIN

SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))

PRINT @dataname_header

SELECT @dataname_header = RTRIM(UPPER(@dataname))

EXEC ("DBCC CHECKDB " + "(" + @dataname + ")")

END

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.

Rate

4.8 (5)

Share

Share

Rate

4.8 (5)