Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List row count of each view and table

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

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:

enter image description here

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.

like image 474
Bill Huang Avatar asked Jun 02 '16 02:06

Bill Huang


People also ask

How do you get the count of all the rows of a table?

The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.

How do I count the number of rows in each column?

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.


1 Answers

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.

like image 123
Ben Thul Avatar answered Sep 22 '22 17:09

Ben Thul