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
 How to remove tables?

Author  Topic 

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-10 : 09:20:55
Hi,

I am using SQL Server 2008 and I would like to remove tables that starts with cas, cat and cps. There are approx. 100 tables.

Is there a way to NOT to do it one by one?

Best Regards.

eg.
cas_report
cat_device
cps_phone

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-10 : 09:33:35
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
+ '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE name LIKE 'cas@_%' escape '@' OR name LIKE 'cat@_%' escape '@' OR name LIKE 'cps@_%' escape '@';

PRINT @sql;
-- EXEC sp_executesql @sql;




--
Chandu
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 09:34:13
Run this code, copy the output to a query window, look over very carefully to make sure that those are the tables you want drop, and then execute it.
SELECT 'drop table ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE
TABLE_NAME LIKE 'cas%'
OR TABLE_NAME LIKE 'cat%'
OR TABLE_NAME LIKE 'cps%'
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-10-10 : 09:35:12
Sure, something like this:


select 'DROP TABLE ' + table_name from INFORMATION_SCHEMA.TABLES
WHERE (table_name like 'cas%' or table_name like 'cat%' or table_name like 'cps%)


You can then copy and paste the result of the above query and drop your tables en masse. Be careful you don't drop the wrong tables though!
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-10-10 : 09:35:42
lol... too slow ;)
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-12 : 02:14:13
thank you
Go to Top of Page
   

- Advertisement -