Gokul's Blog

Sql Query to get the count of tables

Leave a comment

Thanks to wim(commenter) from this blogpost: http://geekswithblogs.net/TakeNote/archive/2007/09/22/115537.aspx

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name
, SCHEMA_NAME(t.schema_id) AS schema_name, 
t.name AS table_name, 
'select * from ' + t.name AS query, 
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

<strong>Tip-2: Slow</strong>
exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''select * from ?'' as table_name from ?'


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s