No of records in each table

Question

How to get a list of all tables with no of records in a particular database in SQL Server.

Thanks

Answer 1

Here's another option - not dependent on INFORMATION_SCHEMA. This would also allow you to alter your where clause (you may edit your @QUERY accordingly).

DECLARE @QUERY VARCHAR(MAX)
SET @QUERY = ''

/*
 * Create a long query with a row count + table name.
 * You may alter your where clause here
 */
SELECT @QUERY =
    @QUERY + ' SELECT COUNT(*), ''' + QUOTENAME(name)
    + ''' FROM ' + QUOTENAME(name) + CHAR(13)
    + 'UNION ALL'
    FROM sys.tables

--Get rid of the last 'UNION ALL'...
SELECT @QUERY = LEFT(@QUERY, LEN(@QUERY) - 10) 

--Prepare a temp table - drop if exists and then create it
IF object_id('tempdb..#TableResults') IS NOT NULL
DROP TABLE #TableResults

CREATE TABLE #TableResults(
    Count INT,
    TableName VARCHAR(MAX) 
);

--Insert the main query result into the temp table
INSERT INTO #TableResults
EXEC(@QUERY);

--Select all from the temp table
SELECT * FROM #TableResults
WHERE COUNT = 0

Answer 2

You will need to use Dynamic SQL and check for existance of rows in each table

declare @sql    nvarchar(max)

select  @sql    = isnull(@sql + ' union all ' + char(13) , convert(nvarchar(max), ''))
        + 'select tbl_name = ''' + name + ''' '
        + 'where not exists (select * from ' + quotename(name) + ')'
from    sys.tables

print   @sql

exec    (@sql)

Answer 3

Did you mean this

SELECT COUNT(*) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN 
(
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE')

Saravanan