15

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
31

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%';
  • Thank you so much for remembering about "arrange the output to drop the tables in a certain order"! – sdlins May 23 '18 at 17:29
3

I ran this query then pasted the results back into query window to drop all the tables:

SELECT 'DROP TABLE ' + NAME from sys.tables
ORDER BY NAME

If you want to delete all tables but keep those with names that started with A, B, C or D:

SELECT 'DROP TABLE ' + NAME from sys.tables
WHERE NAME NOT LIKE '[ABCD]%'
GROUP BY NAME
-1

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 
  • 2
    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
-1

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
  • 2
    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
-2

This can be done using execute as follows:

declare @sql1 nvarchar(max) 
SELECT @sql1 =
 STUFF(
  (
    select ' drop table dbo.[' + name + ']'

FROM         sys.sysobjects AS sobjects
WHERE     (xtype = 'U') AND (name LIKE 'GROUP_BASE_NEW_WORK_%')
        for xml path('')
   ),
        1,1,'')

        execute sp_executesql @sql1
  • 1
    This is essentially a variation, but without any improvement, on the accepted answer. The difference is merely that you've chosen to assume that 1) the default schema is supposed to be dbo, and 2) the names can never contain a ] – both of which may well be true in the OP's case but it would still be a good idea to mention those assumptions, because the caveats that follow from them might not be entirely obvious to other people. Still, as I said at the beginning, my principal issue with this answer is that it just restates an already existing suggestion without adding any new value. – Andriy M Sep 29 '16 at 6:28
-3
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
  • -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 Shah Apr 7 '15 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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