sql-serverdynamic-sqlinformation-schema

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

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

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:

enter image description here

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

I am using SQL Server 2014.

Solution

  • 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;