Drop multiple tables with string

I have tables like lg-010-a..., lg-010-ac...开发者_Python百科, and so on, I have abc database,

I have a command window:

drop table from abc where Table_Name like 'lg-010-%'

Will this drop all the tables starting with lg-010-?

Try something like this:

declare @sql varchar(max)
declare @tablenames varchar(max)

select @tablenames = coalesce(@tablenames + ', ','') + Table_Name from INFORMATION_SCHEMA.TABLES    
where Table_Name like ('lg-010-%')

set @sql = 'drop table ' + @tablenames

exec (@sql)

This queries the INFORMATION_SCHEMA.TABLES table to retrieve table names that match your criteria, then concatenates them together into a comma delimited string.

This string is than added to a 'Drop table ' statement and executed.

Drop table can take multiple comma delimited table names.

(I had originally had this query sys.tables but some research revealed that while they are currently equivalent, the Information_Schema method is quaranteed to work in future versions)

Unfortunately you can't do it like that. One way is:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%lg-010-a%' AND [type] IN ('P')

This will just print out the DROP TABLE statement for each table - you can then copy and paste this output and run it. You can just put an EXECUTE in the loop instead of the PRINT, but I've done it this way so you can see what's going on/check the output first.

I had an issue where the accepted answer was not doing anything. I discovered that I had to add the prefix name of the database to the code to get it to work. If your tables are not dbo.tablename try this.

declare @sql varchar(max)
declare @tablenames varchar(max)

SELECT 
    @tablenames = COALESCE(@tablenames + ', ','') + 'YourDatabaseName.' + Table_Name 

FROM 
    INFORMATION_SCHEMA.TABLES    
WHERE TABLE_TYPE  = 'BASE TABLE' 
    AND TABLE_NAME LIKE 'AP2%'
    AND (RIGHT(TABLE_NAME, 6) < 201708)

SET @sql = 'drop table ' + @tablenames

EXEC (@sql)
GO

Unfortunately you can't do it like that. One way is:

DECLARE @TableName NVARCHAR(128)
SELECT TOP 1 @TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'lg-010-%'
ORDER BY TABLE_NAME ASC

WHILE (@@ROWCOUNT > 0)
    BEGIN
        PRINT 'DROP TABLE [' + @TableName + ']'

        SELECT TOP 1 @TableName = TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE 'lg-010-%'
            AND TABLE_NAME > @TableName
        ORDER BY TABLE_NAME ASC 
    END

This will just print out the DROP TABLE statement for each table - you can then copy and paste this output and run it. You can just put an EXECUTE in the loop instead of the PRINT, but I've done it this way so you can see what's going on/check the output first.

CREATE PROCEDURE dbo.drop_MsSqlTables1 @createDate smalldatetime

AS

declare @flag int =1

declare @tname varchar(50)

declare @sql varchar(max)

select row_number() over (order by name) as num, '[dbo].[' + name +']' as table_name into #temp from sys.tables where name like ('EmpInfo_%') and create_date<@createDate

declare @count int = (select count(*) from #temp)

select * from #temp

while @flag <= @count

    begin

     set @tname = (select table_name from #temp where num = @flag)

     set @sql = 'drop table ' + @tname 

     exec (@sql)

     set @flag = @flag+1
    end