×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I am trying to get a list of all

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

RE: I am trying to get a list of all

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

RE: I am trying to get a list of all

I have been using

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

(OP)
dhookom - This worked perfectly. Thank you.

Jim

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close