Empty Tables

[dzdncnfsd]

Is there any stored procedure I can run that will show me all of the tables with 0 records in a database?

Thanks,
Gladys

Gladys Clemmer

 

[zhavic]

For example that one:

CREATE PROCEDURE get_empty_tables
	
	AS

	DECLARE @cTable_name varchar(100)
	DECLARE @cSQL nvarchar(1000)
	
	CREATE TABLE #empty_tables ( TableName varchar(100) )

	DECLARE c_tables CURSOR LOCAL FAST_FORWARD
		FOR SELECT name 
				FROM dbo.sysobjects 
				WHERE OBJECTPROPERTY( ID, N'IsUserTable') = 1
	
	OPEN c_tables
	FETCH NEXT FROM c_tables INTO @cTable_name
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @cSQL = 'IF ( SELECT COUNT(*) FROM ' + RTRIM( @cTable_name ) + ' ) = 0 INSERT INTO #empty_tables VALUES ( ''' + RTRIM( @cTable_name ) + ''' )'
		EXECUTE ( @cSQL )
		FETCH NEXT FROM c_tables INTO @cTable_name
	END

	CLOSE c_tables
	DEALLOCATE c_tables

	/* result */
	SELECT TableName FROM #empty_tables ORDER BY TableName

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[dzdncnfsd]

Thank you Zhavic. I will give it a try ASAP.
Gladys

Gladys Clemmer

 

[maswien]

try this:


create procedure sp_empty
as
declare @sql as varchar(1024)
declare @t as varchar(256)

set nocount on
drop table #empty
create table #empty(tablename varchar(256), rs int)

DECLARE empty_cursor CURSOR FOR
SELECT name FROM sysobjects where xtype='u'

OPEN empty_cursor
FETCH NEXT FROM empty_cursor INTO @t

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'insert into #empty select ''' + @t + ''', count(*) from ' + '"' + @t + '"'
exec (@sql)

FETCH NEXT FROM empty_cursor INTO @t

END

CLOSE empty_cursor
DEALLOCATE empty_cursor
select * from #empty where rs = 0

set nocount off

 

[dzdncnfsd]

Thank you mjia. Will let you both know how it works out.
Thanks,
Gladys

Gladys Clemmer