List row count of each view and table

Question

I have a database named test which has 2 views and 2 tables in schema dbo like this:

I want to create a table named report which lists the row numbers of 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 test.dbo.report should look like this:

However, I have no idea how to implement. Dynamic SQL is probably the way to go, but somewhat confusing.

I am using SQL Server 2014.

Answer 1

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.