I am trying to get a list of all tables and counts for a data conversion project so I can work to eliminate tables with 0 records and identify core child tables.
Below is code that I put together that retrieves a unique table names from the database and has a field named cnt (count) for me to place the record count into. I am looking for a query that will populate the cnt field with the record count for each table.
Sample (not real) data from the query results is:
Tablename cnt
customer 0
orders 0
Results I am seeking:
Tablename cnt
customer 2214
orders 6040
Code:
USE MyDB
select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
INTO #TEMPSchema
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
order by schema_name,
table_name,
column_id;
select distinct table_name, 0 as cnt from #TEMPSchema order by 1
drop table #TEMPSchemaJim
