sql server - List row count of each view and table -
i have database named test has 2 views , 2 tables in schema dbo this: 
i want create table named report lists row numbers of each view , each table. concept this:
select table_name, table_type, "select count(*) table_name" rowcount test.dbo.report test.information_schema.tables; the test.dbo.report should this:
however, have no idea how implement. dynamic sql way go, confusing.
i using sql server 2014.
you can use dynamic sql build giant chain of union select statements:
declare @sql nvarchar(max) = '' -- use undocumented(?) trick string concatenation in select statement select @sql = @sql + 'union select ' + '''' + table_name + ''' table_name, ' + '''' + table_type + ''' table_type, ' + '(select count(*) ' + table_name + ') [count]' + char(13) + char(10) information_schema.tables -- remove leading "union all" set @sql = right(@sql, len(@sql)-len('union ')) --print @sql -- check what's going executed exec sp_executesql @sql the sql builds , executes looks like:
select 'customers' table_name, 'base table' table_type, (select count(*) customers) [rowcount] union select 'items' table_name, 'base table' table_type, (select count(*) items) [rowcount] union select 'orders' table_name, 'view' table_type, (select count(*) orders) [rowcount] union ... unfortunately cannot execute dynamic sql column in select statement - there nothing allow simple as:
select table_name, table_type, exec('select count(*) ' + table_name) [count] test.dbo.report -- /\== doesn't work test.information_schema.tables; 