Scan All Databases SQL Server

As I was trying to do some database schema cleanup, I needed to scan all databases on a particular server and see if there are any records in the table.  If there were no records this user table is unused and can not be dropped at this point.

This approach also gives a current sampling of certain data situations.  To make changes adjust the variables @table and @schema which are set to the appropriate tables you want to scan.

Key points here are the builiding of the list of databases as a temp table that is then deleted record by record through a while loop instead of a cursor and the use of try/catch on the statement as many of the databases may not have this schema/table combination.

Finally I am saving each valid query statement result into a temp table so that they are aggregated into a single data source I can then apply queries against.

Enjoy.

Scan script

DROP TABLE IF EXISTS #tbl_databases
-- get a list of databases to check
select [s].[Name]
INTO #tbl_databases
from [sys].[databases] as [s]
-- define some variables to use in the loop
DECLARE @table NVARCHAR(MAX) = 'Employee';
DECLARE @schema NVARCHAR(MAX) = 'dbo';
DECLARE @execute_sql NVARCHAR(MAX);
DECLARE @database_name NVARCHAR(500);
DROP TABLE IF EXISTS #tableResults
CREATE TABLE #tableResults 
(
	DbName VARCHAR(2000),
	RecordCount INT
)
-- iterate through each database
WHILE EXISTS (SELECT * FROM #tbl_databases)
BEGIN
    -- get this iteration's database
    SELECT TOP 1 @database_name = [name]
    FROM #tbl_databases
    -- define some dynamic sql to execute against the appropriate database
	SET @execute_sql = 'INSERT INTO #tableResults(DbName, RecordCount) SELECT ''' + QUOTENAME(@database_name) + ''', COUNT(*) FROM ' + QUOTENAME(@database_name) + '.' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);
	BEGIN TRY
		EXEC(@execute_sql);
	END TRY
	BEGIN CATCH
		--PRINT @execute_sql
	END CATCH
    -- delete this database so the loop will process the next one
    DELETE FROM #tbl_databases
    WHERE   [name] = @database_name
END
SELECT *
FROM #tableResults
WHERE RecordCount > 0

Results

DbName	RecordCount
[test]	1