Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 7 May 2013

Query to get all the schema-table names, row-column count inside a database in SQL Server


Query  to get all the schema-table names, row-column count inside a database in SQL Server


Scripts:


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




Screen Shot: