How to execute the same SQL query on each database of a SQL Server instance ?

You want to execute the same SQL query on each database of your SQL Server. For example, because you have one database per customer and want to collect info about your customers’ databases version, size, etc.

The following T-SQL code illustrates how to get the number of tables in each database whose name starts with “RH”, and presents the result in one single recordset.

[code lang=SQL]

DECLARE @rhdb nvarchar(50)
DECLARE @stmt nvarchar(MAX)
DECLARE rh_db_cursor CURSOR

— To get a single result set, each query is concatenated into a single one
— using a union clause. This single query is then executed.
— Watch out : the lenght of the query must not exceed the max size of a nvarchar string
FOR select name from sys.databases where name like ‘RH%’
OPEN rh_db_cursor
FETCH NEXT FROM rh_db_cursor into @rhdb;
PRINT @rhdb
if (@stmt is not NULL)
SET @stmt = @stmt + CHAR(13) + ‘UNION’ + CHAR(13)
SET @stmt = ”

SET @stmt = @stmt + ‘SELECT ”’ + @rhdb + ”’ AS DB_NAME, count(*) AS TABLE_COUNT from [‘ + @rhdb + ‘]..sysobjects where xtype = ”U”’
FETCH NEXT FROM rh_db_cursor into @rhdb;

CLOSE rh_db_cursor;
DEALLOCATE rh_db_cursor;

print @stmt
exec sp_executesql @stmt

The output :

Leave a Reply

Your email address will not be published. Required fields are marked *