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
add comment

3 Answers

up vote 6 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
    
First of all,thanks for your interesting.I want to ask a question.How can I get name list of all tables before drop multiple tables? –  Cell-o Sep 9 '11 at 11:48
    
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%'; –  Aaron Bertrand Sep 9 '11 at 12:06
    
thanks again for your reply. –  Cell-o Sep 9 '11 at 12:10
add comment

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
add comment

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 at 14:18
add comment

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.