I am trying to get a list of all
I am trying to get a list of all
(OP)
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 Jim
Talk To Other Members
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
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]Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: I am trying to get a list of all
Jim