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.
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_reportcat_devicecps_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 |
|
|
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 tWHERE TABLE_NAME LIKE 'cas%' OR TABLE_NAME LIKE 'cat%' OR TABLE_NAME LIKE 'cps%' |
|
|
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! |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-10-10 : 09:35:42
|
lol... too slow ;) |
|
|
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2012-10-12 : 02:14:13
|
thank you |
|
|
|
|
|