list of all tables which is having..

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

list of all tables which is having..

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-11-13 : 01:28:55

Hello

I want to list all tables from database which is having at least 1 records

if table is empty then it should be listed....

Regards

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-13 : 02:56:58

select object_name(id), rows
from sysindexes
where indid in (0,1)
--and rows = 0

Not sure whether you want at least 1 row or none.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-13 : 06:49:08

sysindexes is deprecated, included only for backward compatibility with SQL 2000 and should not be used any longer.

SELECT object_name(object_id) AS TableName, rows
FROM sys.partitions
where index_id in (0,1)

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:00:27

quote:
Originally posted by asifbhura

Hello

I want to list all tables from database which is having at least 1 records

if table is empty then it should be listed....

Regards


CREATE TABLE tablelist
(
ID int IDENTITY(1,1),
TableName varchar(1000)
)

EXEC sp_Msforeachtable 'IF (SELECT COUNT(*) FROM ?)>0 
                        BEGIN INSERT tablelist (TableName) SELECT ''?'' END'

SELECT * FROM tablelist

DROP TABLE tablelist

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources