Filtering a SELECT statement based on row data

Question

I'm trying to add a WHERE clause to a SELECT statement that checks to see if there is data in the table or if it was a 0-row table.

Basically, I'm trying to get this to work but obviously tables.NAME isn't a valid object.

SELECT NAME 
FROM   sys.tables 
WHERE  (SELECT Count(*) 
    FROM   tables.NAME) <> 0

I don't want to have to create a temp table, declare a cursor and checking a value row by row, but I'm having a hard time thinking a bout how to do this otherwise.

score 0 · Answer 1 · edited May 23 '17 at 10:26

Using a cursor is a suitable solution to this problem. You can't dynamically interpret a column from one table as a table name and simultaneously select from that table to see if it's empty like you're attempting to do. In fact you can't even select from a dynamic table without using dynamic SQL. See the documentation for FROM. You can either iterate over each table and check each, or you can query system tables that hold storage statistics to see if tables are empty.

Filtering a SELECT statement based on row data

1 Answers1