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.
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.
|
Recommended Related Reference Material Transact-SQL (William C. Amo) Transact-SQL Programming (Kevin Kline, Lee Gould & Andrew Zanevsky) |
