ROW Count for LIST OF TABLES IN A DATABASE

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

SELECT so.[name] as [table name],
CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name] , si.indid
FROM sysindexes si
INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
Just the table name and no of rows

SELECT so.[name] as [table name],rowcnt as ROWS
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
ON A LINKEDSERVER

SELECT so.[name] as [table name],rowcnt as ROWS
FROM [SERVERNAME].
[DATABASENAME].sys.sysindexes si
INNER JOIN
[SERVERNAME].
[DATABASENAME].sys.sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

-----------------------------------