How to list all non-empty tables

Discussion:

(too old to reply)

darlove

2007-11-21 10:25:54 UTC

Hi. I have searched for an answer to the question and actually found
one but... the text of the reply does not show for some reason.
Therefore I would kindly ask anybody who's in the know to share their
knowledge of the subject: How to list all the non-empty tables in a
database? I presume this is a simple thing but hard for me as I am not
much of an experienced SQL Server geek. Thanx.

Darlove

Shiju Samuel

2007-11-21 11:00:09 UTC

Permalink

Post by darlove
Hi. I have searched for an answer to the question and actually found
one but... the text of the reply does not show for some reason.
Therefore I would kindly ask anybody who's in the know to share their
knowledge of the subject: How to list all the non-empty tables in a
database? I presume this is a simple thing but hard for me as I am not
much of an experienced SQL Server geek. Thanx.
Darlove

Here is a query to start with.
select distinct object_name(id) from sysindexes where rows <> 0

You need to join it with sysobjects to eradicate system tables.

Thanks
Shiju Samuel

darlove

2007-11-21 11:25:24 UTC

Permalink

Post by Shiju Samuel

Here is a query to start with.
select distinct object_name(id) from sysindexes where rows <> 0
You need to join it with sysobjects to eradicate system tables.
Thanks
Shiju Samuel

So then, I have created such something:

select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'

Is this along the lines of you suggestion?

Darek

Tibor Karaszi

2007-11-21 11:52:58 UTC

Permalink

Post by darlove
select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'

No need for the join and the sysobjects table. Also, note that the rows column in versions prior to
2000 isn't guaranteed to be correct, so you might want to go for Uri's suggestion.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by darlove

Post by Shiju Samuel

Here is a query to start with.
select distinct object_name(id) from sysindexes where rows <> 0
You need to join it with sysobjects to eradicate system tables.
Thanks
Shiju Samuel

select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'
Is this along the lines of you suggestion?
Darek

darlove

2007-11-21 13:20:32 UTC

Permalink

On 21 Nov, 11:52, "Tibor Karaszi"

Post by Tibor Karaszi

Post by darlove
select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'

No need for the join and the sysobjects table. Also, note that the rows column in versions prior to
2000 isn't guaranteed to be correct, so you might want to go for Uri's suggestion.
--
Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi

Post by darlove

Post by Shiju Samuel

Here is a query to start with.
select distinct object_name(id) from sysindexes where rows <> 0
You need to join it with sysobjects to eradicate system tables.
Thanks
Shiju Samuel

select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'
Is this along the lines of you suggestion?
Darek- Hide quoted text -

- Show quoted text -

Tibor (are you not Hungarian?),

indeed, no need, but I only realized this after I read your reply,
which
is amazing. If I had thought a little while, I would have not made the
'mistake'. Sometimes the answer to a problem is simple enough to make
a fool of the mind. How often do we look yet we can't see?

Thanks once again for the enlightment. And thank
you all, guys, for your replies.

Darek

Tibor Karaszi

2007-11-21 14:21:48 UTC

Permalink

Post by darlove
Tibor (are you not Hungarian?),

Nope, Swedish. Hungarian name and parents, though. :-)

Post by darlove
indeed, no need, but I only realized this after I read your reply,
which
is amazing. If I had thought a little while, I would have not made the
'mistake'. Sometimes the answer to a problem is simple enough to make
a fool of the mind. How often do we look yet we can't see?

Post by Tibor Karaszi
Also, note that the rows column in versions prior to
2000 isn't guaranteed to be correct, so you might want to go for Uri's suggestion.

Above should have been "prior to 2005".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by darlove
On 21 Nov, 11:52, "Tibor Karaszi"

Post by Tibor Karaszi

Post by darlove
select distinct object_name(ind.id)
from sysindexes ind
join sysobjects obj on ind.id = obj.id
where ind.rows <> 0 and obj.xtype = 'U'

No need for the join and the sysobjects table. Also, note that the rows column in versions prior
to
2000 isn't guaranteed to be correct, so you might want to go for Uri's suggestion.
--
Tibor Karaszi, SQL Server
MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi

Post by darlove

Post by Shiju Samuel

Here is a query to start with.
select distinct object_name(id) from sysindexes where rows <> 0
You need to join it with sysobjects to eradicate system tables.
Thanks
Shiju Samuel

- Show quoted text -

Tibor (are you not Hungarian?),
indeed, no need, but I only realized this after I read your reply,
which
is amazing. If I had thought a little while, I would have not made the
'mistake'. Sometimes the answer to a problem is simple enough to make
a fool of the mind. How often do we look yet we can't see?
Thanks once again for the enlightment. And thank
you all, guys, for your replies.
Darek

Tony Rogerson

2007-11-21 14:46:03 UTC

Permalink

Post by Tibor Karaszi
No need for the join and the sysobjects table. Also, note that the rows
column in versions prior to 2000 isn't guaranteed to be correct, so you
might want to go for Uri's suggestion.

I thought it wasn't fixed until SQL 2005?

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Tibor Karaszi

2007-11-21 17:20:26 UTC

Permalink

Yep, that was a type from my side. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Tony Rogerson

Post by Tibor Karaszi
No need for the join and the sysobjects table. Also, note that the rows column in versions prior
to 2000 isn't guaranteed to be correct, so you might want to go for Uri's suggestion.

I thought it wasn't fixed until SQL 2005?
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Uri Dimant

2007-11-21 10:59:32 UTC

Permalink

Hi

DECLARE @SQLString nvarchar (255),
@ParmDefinition nvarchar (255)

DECLARE @tablename sysname, @Empty char (1)

DECLARE FindNONEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN FindNONEmptyTables

FETCH NEXT FROM FindNONEmptyTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
SET @SQLString = N'IF EXISTS (SELECT * FROM ' + @tablename + ') set
@Empty = ''N'' ELSE set @Empty = ''Y'''
SET @ParmDefinition = N'@tablename sysname, @Empty char(1) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@tablename = @tablename,
@Empty = @Empty OUTPUT

IF @Empty = 'N' PRINT @tablename + ' is NONempty'
FETCH NEXT FROM FindNONEmptyTables INTO @tablename
END

CLOSE FindNONEmptyTables
DEALLOCATE FindNONEmptyTables
GO

Madhivanan

2007-11-21 11:01:54 UTC

Permalink

DBCC Updateusage ('dbname','tablename') with count_rows
select object_name(id) as table_name from sysindexes
where indid<2 and rows=0
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx