demo demo - 4 months ago 7
SQL Question

SELECT name of table if table is empty

I have table with names of tables...

I need to select only that tables that are empty.

For this I've tried to write CURSOR.

DECLARE @Footprint VARCHAR(200)
DECLARE @query VARCHAR(200)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT '[' + [SchemaName] + '].[FootprintBuildState]'
FROM [dbo].[vwAgencySchemas]

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Footprint
WHILE @@FETCH_STATUS = 0
BEGIN
--CHECK IF Table IS EMPTY (UNCORRECT)
SET @query = 'SELECT ' + @Footprint + ' FROM ' + @Footprint + ' WHERE NOT EXISTS (SELECT * FROM ' + @Footprint+ ') '
EXEC (@query)
FETCH NEXT FROM MY_CURSOR INTO @Footprint
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR


This is what I have, but I can't write correct query to select name of empty table

I get Error


Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark after the character string '_test12345678910111213141'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '_test12345678910111213141'.

Answer

Forget that cursor - you can easily do this with just a simple, single SELECT statement - something like this:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    dbo.YourNamesTable tbl ON t.Name = tbl.TableName
WHERE 
    p.Rows = 0
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name
Comments