I am trying to get a list of all 1

[jimoo]

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

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

 

[Olaf Doschke]

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.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[dhookom]

I have been using

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

 

[jimoo]

dhookom - This worked perfectly. Thank you.

Jim