SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to remove tables?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

257 Posts
Posted - 10/10/2012 :  09:20:55  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 10/10/2012 :  09:33:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/10/2012 :  09:34:13  Show Profile  Reply with Quote
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

United Kingdom
199 Posts
Posted - 10/10/2012 :  09:35:12  Show Profile  Reply with Quote
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

United Kingdom
199 Posts
Posted - 10/10/2012 :  09:35:42  Show Profile  Reply with Quote
lol... too slow ;)
Go to Top of Page

raysefo
Constraint Violating Yak Guru

257 Posts
Posted - 10/12/2012 :  02:14:13  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000