Identify empty tables - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Identify empty tables

Author

Topic

dhborchardt
Starting Member

1 Post

Posted - 2010-12-02 : 12:24:29

I need to see what tables have records in them and which ones don't. Is there an easy way to view this without opening or looking into individual properties. I am getting familiar with a new db that has 8000 tables half of which do not have a single record in them.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 12:26:11

if statistics are up to date
select object_name(id) from sysindexes where indid in (0,1) and rows = 0

==========================================
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.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-03 : 18:00:18

[CODE]-----------------------------------------------------------------------------
--- Find table sizes in this database
-----------------------------------------------------------------------------

set nocount on

declare
@PKey int,
@MaxPKey int,
@Name sysname,
@sql nvarchar(1000)

declare @Tbls table (
PKey int identity(1, 1),
Name sysname
)

declare @TblSizes table (
name nvarchar(128), -- Name of the object for which space usage information was requested.
rows char(11), -- Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reserved varchar(18), -- Total amount of reserved space for objname.
data varchar(18), -- Total amount of space used by data in objname.
index_size varchar(18), -- Total amount of space used by indexes in objname.
unused varchar(18) -- Total amount of space reserved for objname but no yet used.
)

insert into @Tbls (Name)
select name
from sys.tables
where (
is_ms_shipped = 0
or
DB_ID() <= 4
)

-----------------------------------------------------------------------------
--- Initialize
-----------------------------------------------------------------------------

select
@PKey = 1,
@MaxPKey = MAX(PKey)
from
@Tbls

-----------------------------------------------------------------------------
--- Main loop
-----------------------------------------------------------------------------

while (@PKey < @MaxPKey)
begin
select
--@sql = N'select ''' + Name + N''', count(*) from dbo.' + Name + N' with (NoLock)'
@Name = Name
from
@Tbls
where
PKey = @PKey

insert into @TblSizes (
name, -- Name of the object for which space usage information was requested.
rows, -- Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reserved, -- Total amount of reserved space for objname.
data, -- Total amount of space used by data in objname.
index_size, -- Total amount of space used by indexes in objname.
unused -- Total amount of space reserved for objname but no yet used.
)
exec sp_spaceused
@ObjName = @Name

-----------------------------------

set @PKey = @PKey + 1
end

select *, N'truncate table dbo.' + Name + N';'
from @TblSizes
/**/where rows = 0
order by
Name[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-04 : 12:43:41

[code]SELECT distinct object_name(object_id) [table], rows
FROM sys.partitions[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources