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_MSFOREACHDB - Databases List Via TSQL

By Brian Knight

Cursors provide a powerful tool for DBAs but sometimes they're not needed. The procedure sp_MSforeachdb with the @command1 through @command3 parameter and is very similary to sp_Foreachtable. Sp_msforeachdb gives a DBA the ability to cycle through every database in your catalog. Although, the procedure was introduced in SQL Server 6+, Microsoft has made major improvements in SQL 7.0. The procedure is especially useful if you'd like to run through a list of databases for standard DBCCs.

In SQL Server 7.0, you can run a DBCC CHECKDB on all databases by using the following :

EXEC sp_Msforeachdb "DBCC checkdb ('?')"

In SQL 6.5 however, you must create a temporary table and in the same session, run a procedure to populate the table. After that, you have the same capabilities as in 7.0, as long as you stay in the same connection. To run sp_MSforeachdb in SQL 6.5, you must run the following:


Create table #SQLOLEDbUserProfile
(dbid int NOT NULL PRIMARY KEY,
profilebits int NOT NULL)
go

sp_MSdbuserprofile 'init'
go

--insert your desired code here (I'm just doing maintenance)
EXEC sp_Msforeachdb "DBCC checkdb ('?')"

As you can see, I'm just performing a DBCC CHECKDB with sp_MSforeachdb. You can also use this procedure for sizing, and placing databases in single user mode. If you have any other ideas for applications, please feel free to contribute them to the DBA to DBA section.

Recommended Related Reference Material
Transact-SQL (William C. Amo)
Transact-SQL Programming (Kevin Kline, Lee Gould & Andrew Zanevsky)


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