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 #TEMPSchema
Well, counts will come from SELECT Count(*) as cnt from databasename.schemaname.tablename.
I'd not count, though, but use sp_spaceused. Also, collecting schematic data for every table isn't always simplest by the sys views or by INFORMATION_SCHEMA, but by sp_MSforeachtable.
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
AND QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) >='[dbo].[%'
GROUP BY
sOBJ.schema_id
, sOBJ.name
-- HAVING SUM(sPTN.Rows) >0
ORDER BY 2 DESC --,[TableName]
RE: I am trying to get a list of all
I'd not count, though, but use sp_spaceused. Also, collecting schematic data for every table isn't always simplest by the sys views or by INFORMATION_SCHEMA, but by sp_MSforeachtable.
Bye, Olaf.
Olaf Doschke Software Engineering
https://www.doschke.name
RE: I am trying to get a list of all
CODE --> SQL
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: I am trying to get a list of all
Jim