Zero row 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 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

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

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-01 : 16:09:11

Perfect thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources