How to find if data has been loaded into database

Here is a simple way to find if the data has been loaded into database without checking every table one after another. This returns a single statement which has count of all the records in all the tables. You can also provide the table schema to narrow your query process.


DECLARE @sql VARCHAR(max), @tableSchema VARCHAR(5)
SET @sql = ''
SET @tableSchema = 'DW'
SELECT @sql = @sql + 'SELECT COUNT(*) as Count,'''+TABLE_NAME+''' AS table_Name FROM '+TABLE_SCHEMA+'.' +TABLE_NAME +' UNION '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @tableSchema
SET @sql = LEFT(@sql,LEN(@sql)-LEN('union'))
SET @sql = @sql+' order by Count'
EXEC (@sql)