July 31, 2007 at 11:26 am
Is there a way to find which tables in the DB has rows in it and select all those tables which has data in it?
Actually I have a large no of tables in a DB where in I wud like to know which tables has data in it and which are empty.
July 31, 2007 at 12:00 pm
Could do something like this:
CREATE TABLE #tbls(
tbl_nameSYSNAME
)
exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM ?) > 0 INSERT #tbls SELECT ''?'' '
SELECT * FROM #tbls ORDER BY tbl_name
DROP TABLE #tbls
July 31, 2007 at 12:31 pm
This will give you a row count for every table.
SELECT so.name, rowcnt
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE indid = 1
AND type = 'U'
July 31, 2007 at 12:51 pm
It's worth noting that if you have tables which don't have clustered indexes on them, they will not be displayed using that method. Lord knows you should never have a table w/out a clustered index... but they frequently exist.
You can change it to WHERE indid <= 1 to include clustered indexes as well as heap data.
July 31, 2007 at 1:03 pm
Wouldn't it faster to run if exists (Select * from ?) instead of count(*)???
July 31, 2007 at 1:04 pm
Keep in mind that this solution doesn't work (is not 100% reliable) if the stats are not up to date.
Also keep in mind that no rows in the table does not mean that it's not ever used. I have quite a few reporting tables here that almost always show row count of 0.... but they are still quite essential to the system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply