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 2000 Forums
 SQL Server Development (2000)
 Zero row tables

Author  Topic 

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-01-31 : 15:38:00
Guys,

Is there anyway from 400 tables database to find out table which have 0 rows.

Any suggestions and inputs would help

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-31 : 16:03:05
Im not sure if there is a more straightforward way of doing it than this:
CREATE TABLE #Results (TableName NVARCHAR(256))

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(256)
SET @TableName = ''

WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

PRINT @TableName

IF @TableName IS NOT NULL
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + ''' FROM ' + @TableName + ' WITH (NOLOCK) HAVING COUNT(*)=0'
)

END

SELECT * FROM #Results

DROP TABLE #Results
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-01 : 10:50:47
DBCC UPDATEUSAGE (0) WITH COUNT_ROWS

Select object_name(id) as table_name from sysindexes
where indid<2 and rows=0




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-01 : 16:09:11
Perfect thank you
Go to Top of Page
   

- Advertisement -