Need a query to find specific tables row counts

Question

Need a query to find specific tables row counts

Summer90

SSC-Dedicated

Points: 32891
More actions
August 8, 2007 at 12:54 pm

#69044

We have a db with about 2,000 tables in them. We are going to purge some data from a list of tables that are like %Time%. What I want to do is have a query that will list tables with TIME in them and give the row counts. There are probably 30 different tables with oddball beginning and ending words for the table names. I cannot seem to figure out what table(s) to query to get this type of data.
It would be something like:
Select tablename,rowcount from sys_____ where table name like '%Time%'
Any ideas on how to skin this cat ?
This is SQL Server 2000.

Viewing 4 posts - 1 through 3 (of 3 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Run this and then run the results from the results pane (you'll have to remove the UNION ALL clause from the last statement).

SELECT 'SELECT ' + CHAR(39) + Name + CHAR(39) + ' as TableName, COUNT(*) as Rows FROM ' + Name + ' UNION ALL'

FROM dbo.SysObjects

WHERE NAME LIKE '%TIME%'

AND xtype = 'U'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

DBCC SHOWCONTIG WITH TABLERESULTS

... will show you a whole lot more than just table names and rows.

If you get negative numbers for rows for some tables, you will need to do a DBCC UPDATEUSAGE for those tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Ah, sorry... that's too much info...

This will do it without a loop... again, if you get negative numbers for rows for some tables, you will need to do a DBCC UPDATEUSAGE for those tables.

SELECT

Owner = USER_NAME(so.UID),

TableName = so.Name,

TableID = so.ID,

Rows = CASE WHEN si.IndID IN (0,1) THEN si.Rows ELSE 0 END,

RowModCtr = si.RowModCtr

FROM dbo.SysObjects so,

dbo.SysIndexes si

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1,

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0 --Not an MS object/table

AND PERMISSIONS(so.ID) <> 0

AND so.Name LIKE '%Time%'

ORDER BY TableName

The RowModCtr column "Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table." The other columns are fairly well self-describing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)