Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 7, 2001

sp_MSforeachtable

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.

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)

Contribute a Tip


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date