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.
The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.
If you need a quick way to count rows that contain data, select all the cells in the first column of that data (it may not be column A). Just click the column header. The status bar, in the lower-right corner of your Excel window, will tell you the row count.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With