Thursday, October 20, 2011

SQL Server : Execute same query against each database or table

How to execute multiple queries against all databases?
Running queries against all the databases?

On several occasion I have had a requirement to run a query against each database or each table, or each index. As long as I can query any system table and create a list of objects I want to run the query for, then you can use the following script.

DECLARE @Database VARCHAR(255)
DECLARE @cmd varchar(1000)

SELECT name FROM master.sys.databases  
WHERE state_desc='ONLINE' and name NOT IN ('master','model','msdb','tempdb','distribution','ReportServerTempDB')   

OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database 
     select 'Current database name is : ', @Database
SET @cmd='use ['+@Database+']; Select * from sys.tables'
EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

You can change the script at the line "SELECT name FROM master.sys.databases " and select anything else you want. Example you want to get a row count on each table then replace that SELECT with

select name from sys.tables 

and the cursor will go through each table.
Now inside the WHILE loop use any command you want. In this case you can have

            Select 'Current Table name is :', @Database
            SET @cmd='select count(*) from '+@Database
            Exec (@cmd)

Obviously you can change the reference to @Database to @Table or anything other object that you want to use. I find this method to be easier than using msforeachdb and msforeachtable since I can run it for all indexes, columns or anything else I want.

No comments:

Post a Comment