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

SP_MSFOREACHDB - Getting a list of databases 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 Server 7.0 and 2000. The procedure is especially useful if you'd like to run through a list of databases for standard DBCCs.

In SQL Server 7.0 and 2000, 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 and 2000, 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.

Total article views: 13217 | Views in the last 30 days: 20
 
Related Articles
BLOG

DBCC CHECKDB

Quite often when I see a new SQL Server setup, a CHECKDB has never been run on the databases. It’s.....

FORUM

CHECKDB

CHECKDB

FORUM

DBCC CHECKDB - System databases

DBCC CHECKDB - System databases

FORUM

'sp_MSforeachdb' expects parameter '@command1',

'sp_MSforeachdb' expects parameter '@command1'

FORUM

dbcc checkdb

dbcc checkdb

Tags
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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