Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query?

something like that table names:

LG_001_01_STLINE, 
LG_001_02_STFICHE
share|improve this question

4 Answers 4

up vote 11 down vote accepted

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course there are potential gotchas, for example if these tables have foreign key relationships, you'll either need to drop them first, or arrange the output to drop the tables in a certain order.

To just get the list of tables, use:

SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';
share|improve this answer
SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';

Run above query and save results into a csv. Then open that CSV in a notepad. Then do Ctrl+H to replace schema with DROP TABLE SCHEMA that will give you all the drop queries, copy and paste this big sql into your sql tool and execute

if your results are

myschema.table1
myschema.table2

after replace, it'll look like this

DROP TABLE MYSCHEMA.TABLE1
DROP TABLE MYSCHEMA.TABLE2
share|improve this answer
    
-1 Why would you do copy/paste in Excel and generate drop commands ? You can easily do it using PRINT statement. How is your answer better than the highest voted answer ? –  Kin Apr 7 at 19:30

I like this one that I wrote:

  DECLARE @chv_LG001_TableName nvarchar (100)
  DECLARE @chv_DROP_LG001_Tables nvarchar(100)
  DECLARE @chv_LG001_Table_Count int

  SET @chv_LG001_Table_Count = (SELECT count(OBJECT_NAME(id))
        FROM SYSINDEXES
        WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2
            and OBJECT_NAME(id) like 'LG_001%')

 IF @chv_LG001_Table_Count > 0
    BEGIN

    DECLARE  Drop_LG001_Tables_Cursor CURSOR FOR
      -- This query will give you the table list you are wanting
        SELECT OBJECT_NAME(id)
        FROM SYSINDEXES
        WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2
            and OBJECT_NAME(id) like 'LG_001%'

    OPEN Drop_LG001_Tables_Cursor
    FETCH NEXT FROM Drop_LG001_Tables_Cursor INTO @chv_LG001_TableName 
    WHILE @@FETCH_STATUS = 0 

    BEGIN           

    SET @chv_DROP_LG001_Tables = 'DROP TABLE ' + '[' + @chv_LG001_TableName + ']'

    --Print @chv_DROP_LG001_Tables 
-- Uncomment the next line when you are ready because it WILL clear out these tables. 
    --EXEC sp_executesql @chv_DROP_LG001_Tables

    FETCH NEXT FROM Drop_LG001_Tables_Cursor
    INTO @chv_LG001_TableName

    END

    CLOSE Drop_LG001_Tables_Cursor
    DEALLOCATE Drop_LG001_Tables_Cursor

END
share|improve this answer
    
So many issues here. (1) The cursor scaffolding is complex and unnecessary. (2) When you do use a firehose cursor, you should at least use STATIC and/or LOCAL FAST_FORWARD](sqlperformance.com/2012/09/t-sql-queries/cursor-options). (3) You shouldn't be using deprecated, backward compatibility views like sysindexes. (4) Your script assumes all tables are in the dbo schema (or worse, the default schema of the executing user, which may not even be dbo). –  Aaron Bertrand Jan 7 '14 at 14:18

This allows you to delete a much greater number of tables.

declare 
@cursor as cursor, 
@FTABLE as varchar(500) 
set @cursor = CURSOR FOR 
select 'drop table ' + NAME + ';' 
from sys.tables 
where not SUBSTRING(NAME,10,3) in 
( 
'310', 
'311', 
'312', 
'313', 
'314', 
'320', 
'321', 
'322' 
) 
open @cursor 
fetch next from @cursor into @FTABLE 
while (@@FETCH_STATUS =0) 
begin 
        exec(@FTABLE) 
        print @FTABLE 
fetch next from @cursor into @FTABLE 
end 
close @cursor 
deallocate @cursor 
share|improve this answer
1  
Greater than what? Could you modify your SQL so that it looks for tables with a common prefix, like OP asked for? –  dezso Nov 9 '12 at 22:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.