Find which MS-SQL tables have the most rows

 

SELECT
    t.[name] AS TableName,
    ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) AS TableRowCount
FROM
    sys.tables t
    LEFT JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)
ORDER BY i.rows

 

….and then create a database script for clearing them out:

 

SELECT
DeleteStatement = CASE
        WHEN fk.object_id IS NULL
        THEN ‘TRUNCATE TABLE ‘
+ t.[name] + ‘ — ‘ + ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) + ‘ rows’
        ELSE ‘DELETE ‘ + t.[name] + ‘ — ‘ + ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) + ‘ rows’
    END
FROM
    sys.tables t
    LEFT JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)
    LEFT JOIN sys.foreign_keys fk ON (fk.parent_object_id = t.object_id)

ORDER BY i.rows DESC

 
 

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s