August 21, 2009 at 11:11 am
Hi,
I'm just wondering if anyone has ever tried to list the tables in their database that actually contained data?
I've created a 2 column table to catch results.
Well..... actually I created a 1 column table at first and used INFORMATIONSCHEMA to populate it with the list of table name like this:
insert into X_TABLE_LIST
Select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME not like '%_VIEW'
Then I went back and added a second Column called RECORDS. So now X_TABLE_LIST is really a 2 column table.
I'm at a loss as to how I can insert the results from something like select count(*) into the RECORDS column of my table wherever the TABLE_NAME column of INFORMATION)SCHEMA.TABLES equals the TABLE_NAME in my X_TABLE_LIST table.
If successful my little X_TABLE_LIST table would look something like this:
TABLE_NAME RECORDS
ACCOUNTS 10022
STUDENTS 12003
COURSES 910
ADDRESSES 12000
EVENTS 0
STAFF 400
With this information I would be able to Identify that table(s) that have records and the tables that dont without having to go through 4000 tables by hand.
I'm probably over explaining it, but I hope you get the jist.
Thanks,
Chris
August 21, 2009 at 11:26 am
I use the following script to pull a list of tables and space used. Add a HAVING clause to only return those tables with > 0 rows, and it should provide what you're looking for.
-Eddie
SELECT object_name(s.object_id) as
,
sum(CASE WHEN index_id 10000 --AND index_id < 2
GROUP BY OBJECT_ID
Eddie Wuerch
MCM: SQL
August 21, 2009 at 11:45 am
Try this.
select o.name as "Table Name",i.rowcnt as "Row_Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1) and o.name not like 'sys%' and o.name not like 'dt%' and i.rowcnt 0
order by i.rowcnt desc
SQL DBA.
August 21, 2009 at 12:03 pm
Eddie Wuerch (8/21/2009)
I use the following script to pull a list of tables and space used. Add a HAVING clause to only return those tables with > 0 rows, and it should provide what you're looking for.-Eddie
SELECT object_name(s.object_id) as
,
sum(CASE WHEN index_id 10000 --AND index_id < 2
GROUP BY OBJECT_ID
Fits like a glove!!
Big Thanks!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply