List all tables for a datasbase with row counts AND owner name

Question

Post reply

List all tables for a datasbase with row counts AND owner name

Jpotucek

SSCoach

Points: 18583
More actions
May 5, 2009 at 8:50 am

#75463

MS SQL Server 2000 -
I ran this to list all tables and their row counts for a Database.
select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows from sysindexes a inner join sysobjects b on a.id = b.id
Can anyone tell me please how to modify this so that it also returns the DB owner for each table?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try this. I think this will work, but I do not have a 2000 instance to test with.

select

a.rows , c.*

from sysindexes a

inner join INFORMATION_SCHEMA.TABLES C

on object_name(a.id) = C.TABLE_NAME

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Thanks... I have about 60 rows in this one table and your select returned over 400 rows.. duplicated most of the tables many times.. I'll keep playing around with it..

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

Ok. I played around with it a little more. Try this one.

Select DISTINCT b.rowcnt, A.*

FROM INFORMATION_SCHEMA.TABLES A

JOIN sysindexes B on OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) = B.id

WayneS SSC Guru Points: 95460 More actions · Answer 4

Well, this might give you a little more information than what you asked for. Not only does it include the owner name, but also the table name, as well as the # of rows in each table.

if OBJECT_ID('tempdb..#DBCounts') is not null DROP TABLE #DBCounts

create table #DBCounts (TableName sysname, RowQuantity int)

execute sp_msforeachtable 'insert into #DBCounts select ''?'', count(*) from ?'

select * from #DBCounts

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 5

this one runs quicker for me...displays only user tables

SELECT

a.name, object_name (i.id) TableName, rows as RowCnt

FROM sysindexes i INNER JOIN sysobjects o ON (o.id = i.id AND o.xType = 'U')

inner join sys.sysusers a on o.uid = a.uid

WHERE indid < 2

ORDER BY 3 desc, TableName

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Thank you all so much for your help!!! The last one worked best for me!

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Just make sure you have updates statistics before you query for RowCnt from sysindexes.

It does not reflect real number of records all the time.

_____________
Code for TallyGenerator

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

Sergiy (5/5/2009)
Just make sure you have updates statistics before you query for RowCnt from sysindexes.
It does not reflect real number of records all the time.

Yes with

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.

Madhivanan

Failing to plan is Planning to fail