Finding All empty tables in SQL db

Had to do this at work figured this script might be useful to some around here. If you are and tester and gets a new system is a good way to find out what tables are getting no activity.

DECLARE @Tables TABLE
(
Name nvarchar(100)
)
DECLARE @EmptyTables TABLE
(
Name nvarchar(100)
)
DECLARE @AlmostNoRowsTables TABLE
(
Name nvarchar(100)
)

INSERT INTO @Tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

DECLARE @controlVariable nvarchar(100)
DECLARE @counter int
DECLARE @rowsleft int
SELECT Top 1 @controlVariable = Name FROM @Tables

Declare @SQL nVarChar(1000)
Declare @ex nVarChar(1000)
SELECT @SQL = ‘SELECT @counter=COUNT(*) FROM ‘

WHILE(@rowsleft > 0)
BEGIN
SELECT @ex = @SQL + @controlVariable

EXEC SP_EXECUTESQL @ex,N’@counter INT OUTPUT’,@counter OUTPUT

if(@counter = 0)
BEGIN
SELECT ‘Empty Table’ +@controlVariable
END

if(@counter > 0 AND @counter <10)
BEGIN
SELECT ‘Table has almost no rows’ +@controlVariable
END
DELETE @Tables WHERE Name = @controlVariable
SELECT Top 1 @controlVariable = Name FROM @Tables
SELECT @rowsleft = COUNT(*) FROM @Tables
PRINT @rowsleft
END

Leave a Reply