Execute an sql statement at all database of an sql server instanct.

In this post I will write about a problem I had. I needed to find a value in a specific name of a client. I have many similar databases but the data in the databases differ. So, I needed to execute a SQL statement at every database to find the right value. After some searching I found the sp_msforeachtable system stored procedure. With this procedure you can execute SQL code at every database at you SQL server instance.

Here are the examples I needed to find the customer with the customerId 2 in all databases.

--select customerId in the customer table at every database at the sql server instance
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, * FROM Customers where CustomerId = 2'; 
EXEC sp_MSforeachdb @command
GO

Here you see the result, I found it in two databases (master and Preparation).

After I solved that problem I tried some different things with the sp_MSforeachdb procedure. In the next example I select all databases with name, filepath and size of the file.

--select all databases with name, filename and size
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, SF.Name as Name, 
SF.filename as Filename, SF.size as Size FROM sys.sysfiles SF'
EXEC sp_MSforeachdb @command
GO

Here you see the result of my SQL server instance.

After that i tried to select all tables in all databases that start with an p. As you see u can use the ? as an placeholder for the database name of the actual database.

--select all tables that start with p
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, '' '' as Name, '' '' as xtype UNION 
SELECT DB_NAME() DBName, Name, xtype FROM SYSOBJECTS WHERE xtype = ''U'';'
EXEC sp_MSforeachdb @command
GO

Here you can see the result. All tables at all databases that start with a p.

Then I thought about what I could do with that procedure and while searching the internet for further good examples I discovered the sp_MSforeachtable procedure. That procedure does not execute the command for every database it executes the command for every table in the actual database. That means the ? is the placeholder for the tablename. Here are some examples what you can do with them.

--select the number of rows in every table 
EXEC sp_MSforeachtable 'Select ''?'' as tableName, 
count(1) numRecords from ?';

--space used of every table 
EXEC sp_MSforeachtable 'exec sp_spaceused [?]';


Two further idears to use the sp_MSforeachtable procedure are to rebuild the index of every table in the database and to update the statistics for all tables at the database.

--update all statistics in all Tables in a database
EXEC sp_MSforeachtable 'update statistics ? with all';

--rebuild all indexes for all tables in a database
EXEC sp_MSforeachtable 'print ''?'' DBCC DBREINDEX(''?'','''',80)';
Advertisements