Below is the SQL we can use to get the tables and its row count.
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
drop table #counts
Alternatively, can use this SQL, it will give the same result:
DECLARE @sql nvarchar(MAX)
SELECT
@sql = COALESCE(@sql + ' UNION ALL ', '') +
'SELECT
''' + s.name + '.' + t.name + ''' AS ''Table'',
COUNT(*) AS Count
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
ORDER BY
s.name,
t.name
EXEC(@sql)
Reference: here
No comments:
Post a Comment