Drop tables with particular conditions

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

Transact-SQL (2008)

Drop tables with particular conditions

Author

Topic

kusanagirong
Starting Member

4 Posts

Posted - 2010-07-04 : 18:54:46

hi,

I was trying to drop a group of table with particular conditions
e.g The database has tables:
AAA_1
AAA_2
XXX_3
AAA_4
AAA_5
XXX_6
and I only want to drop tables NOT start with XXX_
but I think there is not statement (DROP TABLE ? WHERE ? NOT LIKE(...)) like

DECLARE @count INT;

SET @count = SELECT COUNT(*) FROM sys.objects WHERE TYPE = 'U' AND NAME<> 'dtproperties';

WHILE @count > 0
BEGIN
DROP TABLE ? WHERE ? NOT (LIKE XXX_)
SET @count = SELECT COUNT(*) FROM sys.objects WHERE TYPE = 'U' AND NAME<> 'dtproperties';
END

any1 knows how to do that?

Thanks advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-05 : 00:15:31

One way to accomplish the task:

SELECT
'DROP TABLE ' +
S.NAME
FROM SYSOBJECTS S WHERE XTYPE ='U' AND NAME not LIKE 'dtproperties'

--To drop table not starting with 'XXX'
SELECT
'DROP TABLE ' +
S.NAME
FROM SYSOBJECTS S WHERE XTYPE ='U' AND NAME not LIKE 'XXX%'

Copy the output of the select statement and execute it.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources