SQLServer All Table Counts: All Databases

Below is a script that gets all of the counts for all of the tables on a server. Pretty useful if you are looking for some rows to delete. Commented to explain how it works.

Hope that helps, Jon Bosker, DB Gurus, 17 Nov 2015

-- We are going to save the results on master
USE master 

-- Create the tables to stpore the results
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbgDatabases')
DROP TABLE dbgDatabases
GO
CREATE TABLE dbgDatabases
(
id int identity,
Databasename varchar(200),
)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbgRowCounts')
DROP TABLE dbgRowCounts
GO
CREATE TABLE dbgRowCounts
(
Databasename varchar(200),
TableName varchar(200), 
NumberOfRows int,
DateStamp datetime
)

-- Get a list of all of the user databases
INSERT INTO dbgDatabases
Select [name] from Sys.Databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB','mail','WebsitePanel')

DECLARE @sSQL1 varchar(8000)
DECLARE @nCounter1 int
SET @nCounter1 = 1

-- Build the SQL
-- I got this SQL from http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database
WHILE EXISTS(SELECT * FROM master.[dbo].[dbgDatabases] WHERE id >= @nCounter1)
BEGIN
SELECT  @sSQL1 = 'INSERT INTO master.[dbo].[dbgRowCounts] (Databasename, TableName, NumberOfRows, DateStamp) 
SELECT ''' + Databasename + ''', o.name, ddps.row_count, ''' + CAST(GETDATE() AS varchar) + '''
FROM [' + Databasename + '].sys.indexes AS i
INNER JOIN [' + Databasename + '].sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN [' + Databasename + '].sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME'
FROM master.[dbo].[dbgDatabases] 
WHERE id = @nCounter1
PRINT @sSQL1 
EXEC (@sSQL1)
SET @nCounter1 = @nCounter1+1
END

-- Show the results - I am showing biggest first
SELECT * FROM master.[dbo].[dbgRowCounts] ORDER BY NumberOfRows DESC