SQL query to fetch all schema/table names and row/column count inside a database

SQL query to fetch all schema/table names and row/column count inside a database.

Try out the below sql query to get all the schema/table names, row/column count inside a database

 

[use database name]

 

select  [SchemaName],[TableName],[RowCount], ColumnCount

from    (select s.name [SchemaName], t.name [TableName], sum(st.row_count) [RowCount]

from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

inner join sys.indexes i on t.object_id = i.object_id

inner join sys.dm_db_partition_stats st on t.object_id = st.object_id and i.index_id = st.index_id

where i.index_id < 2

group by s.name, t.name

) t_r

 

inner join 

(select TABLE_NAME, COUNT(TABLE_NAME) ColumnCount from INFORMATION_SCHEMA.COLUMNS group by TABLE_NAME) t_c

on   t_r.TableName = t_c.TABLE_NAME

Output: