Counting the number of rows per view and table on SQL Server

Question

I have a database named test which contains 2 views and 2 tables under the schema dbo:

I want to create a table named report which lists the number of rows for each view and each table. The concept is like this:

select table_name, table_type, "select count(*) from table_name" as rowCount
into test.dbo.report
from test.INFORMATION_SCHEMA.tables;

The expected output (test.dbo.report) should look like this:

I am still trying with Dynamic SQL but having no luck so far.

I am using SQL Server 2014.

Undeniable

what result you are expecting? – Tammeratammi

Answer 1

You can use dynamic SQL to build a giant chain of union all select statements:

declare @sql nvarchar(max) = ''

-- use an undocumented(?) trick with string concatenation in a select statement
select @sql = @sql + 'union all select ' + 
    '''' + TABLE_NAME + ''' as TABLE_NAME, ' + 
    '''' + TABLE_TYPE + ''' as TABLE_TYPE, ' + 
    '(select count(*) from ' + TABLE_NAME + ') as [COUNT]' + 
    char(13) + char(10) 
from INFORMATION_SCHEMA.TABLES

-- remove leading "union all"
set @sql = right(@sql, len(@sql)-len('union all '))

--print @sql    -- to check what's going to be executed

exec sp_executesql @sql

The SQL it builds and executes looks like:

select 'customers' as TABLE_NAME, 'BASE TABLE' as TABLE_TYPE, (select count(*) from customers) as [rowcount]
union all select 'items' as TABLE_NAME, 'BASE TABLE' as TABLE_TYPE, (select count(*) from items) as [rowcount]
union all select 'orders' as TABLE_NAME, 'VIEW' as TABLE_TYPE, (select count(*) from orders) as [rowcount]
union all ...

Unfortunately you cannot execute dynamic SQL as a column in a select statement - there is nothing that would allow you to do anything as simple as:

select table_name, table_type, exec('select count(*) from ' + table_name) as [count]
into test.dbo.report --         /\== doesn't work
from test.INFORMATION_SCHEMA.tables;

Duarte

Answer 2

Since you're specifically using SQL Server, you don't have to artificially constrain yourself to using information schema. The information you need is in the dynamic management views. Or, one view specifically:

select object_name(object_id), sum(rows) 
from sys.partitions
where index_id in (0, 1)
group by object_id;

The row count is approximate, but it's usually pretty close in my experience. You do have the benefit of not having to scan every table's data just to get a count. Note: this won't work for views unless the view is an indexed view.

Elaboration

Answer 3

Run below query

    DECLARE     @SQL VARCHAR(MAX);
    SET         @SQL = '';

    SELECT @SQL = @SQL + 'INSERT INTO test.dbo.report SELECT ''' +
                 TABLE_SCHEMA + '.' + table_name +  ''', ''' + table_type + ''', COUNT(*) FROM ' +
                 TABLE_SCHEMA + '.' + table_name +
                 CHAR(13)  from INFORMATION_SCHEMA.tables

    EXEC (@SQL)

Venatic

Invalid object name 'test.dbo.report'. – Undeniable

you have created table "report" under test database as per your question update "test.dbo.report" to your data base name .schemaname.table name" – Venatic

Worked, with create table test.dbo.report ( table_name varchar(50), table_type varchar(50), rowCnt int ); prepended. – Undeniable

I created a stored procedure to get record counts of all views in a server for a database, using your program, and adding variables for server and database names. I'm able to add variable names for count() but with Information.Schema.tables I am getting a syntax error. I will be grateful if you could help me. Ex: INSERT INTO ##temptbl SELECT ''' + @SchemaName + '.' + table_name + ''', ''' + @SERVER + ''', COUNT() FROM ['+ @SERVER+''+'].['+@DATABASE+''+'].'+ @SchemaName + '.' + table_name + Char(13) from [ServerName].[DatabaseName].INFORMATION_SCHEMA.tables " – Bunns