Executing the Same SQL Statement on ALL SQL Databases

From time to time you may need to execute the same SQL Statements on ALL SQL Databases on your Server. Better yet, you may even need to have the Database Names to match a specific pattern.

There are two ways to accomplish this:

1 - Using sp_MSforeachdb - This is a hidden Microsoft SQL feature that loops through all databases for you and allows you do execute a query on each database.



2 - Using a Cursor in a While Loop - This is essentially the old fashion way, by looping through all the databases using a loop and executing the command.



In the above code:

DB_Pattern - Partial String you want to find in the database name.
TABLENAME - Name of the Table you want to get a count from.
DB_Name - Variable that stores the Database Name

Comments or questions? Post them below.

Thanks,

Pete Soheil
DigiOz Multimedia, Inc.
www.digioz.com