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]
USE MASTER

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;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @rhdb
if (@stmt is not NULL)
SET @stmt = @stmt + CHAR(13) + ‘UNION’ + CHAR(13)
ELSE
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;
END

CLOSE rh_db_cursor;
DEALLOCATE rh_db_cursor;

print @stmt
exec sp_executesql @stmt
[/code]

The output :