So I had a problem where something was not working in a staging environment but fine in dev. So I wanted to compare about 300 tables in a database to a different copy of it to see if a table was empty somewhere that was causing the error. Still haven't found the error yet. Also, the reason I'm doing so is because I'm working with a proprietary API library that manages to obfuscate which tables are being called.
To get the list of empty tables, we can use the below tsql –
EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '
And, to get a list of tables having at least one row of data, we can use the below tsql –
EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '
Major thanks to