Deleting empty tables in sql database?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Deleting empty tables in sql database??

Author

Topic

turkalpk
Starting Member

21 Posts

Posted - 2006-04-02 : 20:06:00

Hello I have 16000 tables in a sql database and I need a sql query command to delete empty tables from that sql database please help.

F16 LÝGHTÝNÝNNNG

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-02 : 20:17:25

You mean drop?

something like

declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name < min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int', @i int out
if @i = 0
begin
select @sql = 'drop table [' + @name + ']'
exec (@sql)
end
end

You could also run dbcc updateusage then use rows in sysindexes to find the empty tables.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

turkalpk
Starting Member

21 Posts

Posted - 2006-04-03 : 04:49:17

quote:
Originally posted by nr

You mean drop?

something like

declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name < min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int', @i int out
if @i = 0
begin
select @sql = 'drop table [' + @name + ']'
exec (@sql)
end
end

You could also run dbcc updateusage then use rows in sysindexes to find the empty tables.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hello nr I executed your command and it returned errors

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '<'.
Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'int'.

Thanks ...

F16 LÝGHTÝNÝNNNG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-03 : 06:19:51

[code]
declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name = min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int out', @i out
if @i = 0
begin
select @sql = 'drop table [' + @name + ']'
print @sql
-- unmask next to drop the table
-- exec (@sql)
end
end[/code]

KH
Choice is an illusion, created between those with power, and those without.

turkalpk
Starting Member

21 Posts

Posted - 2006-04-03 : 10:07:30

quote:

Originally posted by khtan


declare @name varchar(128), @sql nvarchar(2000), @i int
select	@name = ''
while 	@name < (select max(name) from sysobjects where xtype = 'U')
begin
	select @name = min(name) from sysobjects where xtype = 'U' and name > @name
	select @sql = 'select @i = count(*) from [' + @name + ']'
	exec sp_executesql @sql, N'@i int out', @i out
	if @i = 0
	begin
		select @sql = 'drop table [' + @name + ']'
		print @sql
                -- unmask next to drop the table
		-- exec (@sql)
	end
end

KH
Choice is an illusion, created between those with power, and those without.

KH thanks a lot it executed well tables reduced to 65034 but there are still empty tables in database.I don't understand..

F16 LÝGHTÝNÝNNNG

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 10:11:11

Maybe they are candidates for Foreign Key constraints, and thus can't be dropped even though they are empty?

If you generated a script that output

PRINT 'MyTableName'
GO
DROP MyTableName
GO

then you would see the table name (from the PRINT command) and then see any error from the DROP command, so you would know which ones had not been dropped and what the error message was.

Kristen

turkalpk
Starting Member

21 Posts

Posted - 2006-04-03 : 19:23:34

quote:

Originally posted by khtan


declare @name varchar(128), @sql nvarchar(2000), @i int
select	@name = ''
while 	@name < (select max(name) from sysobjects where xtype = 'U')
begin
	select @name = min(name) from sysobjects where xtype = 'U' and name > @name
	select @sql = 'select @i = count(*) from [' + @name + ']'
	exec sp_executesql @sql, N'@i int out', @i out
	if @i = 0
	begin
		select @sql = 'drop table [' + @name + ']'
		print @sql
                -- unmask next to drop the table
		-- exec (@sql)
	end
end

KH
Choice is an illusion, created between those with power, and those without.

I have just realized that I didn't unmask
-- exec (@sql)
when I unmasked and executed it finaly there is no empty table THANKS ....

F16 LÝGHTÝNÝNNNG

turkalpk
Starting Member

21 Posts

Posted - 2006-04-08 : 02:08:32

quote:
Originally posted by turkalpk

quote:
Originally posted by khtan

declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name = min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int out', @i out
if @i = 0
begin
select @sql = 'drop table [' + @name + ']'
print @sql
-- unmask next to drop the table
-- exec (@sql)
end
end

KH
Choice is an illusion, created between those with power, and those without.

I have just realized that I didn't unmask
-- exec (@sql)
when I unmasked and executed it finaly there is no empty table THANKS ....

F16 LÝGHTÝNÝNNNG

Hi I want to modify above query to delete tables those each table contains only one row and also delete empty tables , how can I do it?

F16 LÝGHTÝNÝNNNG

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-08 : 02:32:57

declare @name varchar(128), @sql nvarchar(2000), @i int
select @name = ''
while @name < (select max(name) from sysobjects where xtype = 'U')
begin
select @name = min(name) from sysobjects where xtype = 'U' and name > @name
select @sql = 'select @i = count(*) from [' + @name + ']'
exec sp_executesql @sql, N'@i int out', @i out
if @i <= 1
begin
select @sql = 'drop table [' + @name + ']'
print @sql
-- unmask next to drop the table
-- exec (@sql)
end
end

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources