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.
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
DbName RecordCount [test] 1