Get number of rows for tables across all databases

Question

Searched a bit and didn't see anything that really fit my needs (although I don't really understand SQL beyond the basic select statement so maybe I just missed it). Took a SQL class in school many moons ago.

I have:

• Virtual Windows Server 2008 R2 running SQL 2008 R2. I connect via MS SQL Server Management Studio
• 90-100 databases
• All DB's have the same table structure (each one is a different client)

I want to:

• Search all databases and return a list of all databases which have a table (TableName) that is larger than say, 5000 records.
• Some sort of script that I can schedule that will use that list and if it finds a DB that the TableName table is more than 5000 records, will delete anything older than x amount of days (say 30). Any kind of logging to know what happened over the last few days would be a bonus.

Any help would be appreciated. Thank you.

EDIT/UPDATE (2/24/15): Hiren Dhaduk provided a nice stored procedure that works. Thanks!

Answer 1

You can use following store procedure in any database. Then run this store procedure by passing table name and NoOfRows(Minimum number of rows for table , In your case it will be 5000):

CREATE PROCEDURE usp_FindLargeTables
@TableName VARCHAR(256) , @NoOfRows int
AS
BEGIN
    DECLARE @DBName VARCHAR(256)
    DECLARE @varSQL VARCHAR(512)
    DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
WHERE state != 6

CREATE TABLE #TmpTable (TableName VARCHAR(256),
SchemaName VARCHAR(256),
DBName VARCHAR(256))

OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

    SET @varSQL =  'USE ' + @DBName + ';
    INSERT INTO #TmpTable
    SELECT 
    sysobjects.Name as TableName
    , sysindexes.Rows as NoOfRows , '''+ @DBName + ''' AS DBName
    FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
    WHERE
    type = ''U''
    AND sysindexes.IndId < 2 
    and sysobjects.Name = '''+ @TableName +''' and sysindexes.Rows > ' + CONVERT(VARCHAR, @NoOfRows) + ''

EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

SELECT *
FROM #TmpTable
WHERE DBName != 'master'

-- STEP 2
DECLARE @DYNAMICQUERY VARCHAR(MAX)

SET @DYNAMICQUERY = 
REPLACE((
SELECT 'DELETE FROM ['+ DBName +'].[dbo].['+ TableName +'] where Createdate   < DATEADD(day, -30, GETDATE());'
FROM #TmpTable
WHERE DBName != 'master'
FOR XML PATH('')
), '&lt;' , '<');

EXEC(@DYNAMICQUERY);

DROP TABLE #TmpTable
END

Example : usp_FindLargeTables 'DeltaStuds',5000

Clarification on your second point :

Unless you run a trace when the changes happen it is not possible.

So to do this i would suggest you to put one column called createdate in this table and then you will be able to delete record created before 30 days.

Answer 2

What you are asking is very custom to your setup...

You could use the following query to identify tables with row count greater than 5000

SELECT sc.name +'.'+ ta.name TableName
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 and ta.name = 'DeltaStuds'
 GROUP BY sc.name,ta.name
 having SUM(pa.rows)>5000

But you would need something like this link to run it for all DBs. How to find column names for all tables in all databases in SQL Server. I dont know of a easier way...

For the second part, you will have to setup a delete process that takes the resultset from the above query, creates a dynamic query and deletes rows based on your date field. In that process, you could inject an audit mechanism that logs that delete action, row count, datetime, etc...