Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LoriM
Starting Member
29 Posts |
 Posted - 2005-04-22 : 12:19:00
|
| Hi,I have a quick question. I have a vendor database with over 200 tables. We are trying to figure out which tables we are actually using, as a random check shows alot of them to be empty.Is there a T-SQL statement I can use to show me only the tables that are populated in a particular database? Thanks for any help |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-22 : 12:34:42
|
USE yourDbNameEXEC sp_updatestatsselect name, rowcnt from sysindexesthis will of course work only on tables that have indexes.Go with the flow & have fun! Else fight the flow  |
 |
|
|
LoriM
Starting Member
29 Posts |
Posted - 2005-04-22 : 16:08:30
|
| Thanks for your help, spirit1!I also used another procedure from Bill Graziano that shows the largest tables in a db and their row count (among other things).It's called BigTables.sql if anybody is looking for it. You can designate how many tables you want to see. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-22 : 19:08:50
|
quote:
EXEC sp_updatestats
Actually, you want to run DBCC UPDATEUSAGE(0) to update the rowcounts on tables.quote:
this will of course work only on tables that have indexes.
Not true. Non-clustered tables with no indexes will still have an entry in sysindexes that will contain a rowcount. The modified query below will do the trick:select object_name(id) as table_name, rows from sysindexes where id>100 and indid<2 |
|
|
|
|
|
|
Show me populated tables