Drop tables that starts with specific characters

Question

I have multiple table names like g_str_a , g_str_ab , g_str_abc . I would like to drop all those tables that start with g_str on SQL Server 2008.

Will DROP Tables like 'g_str' help?

Please help me with the script.

score 4 · Answer 1 · edited May 23 '17 at 12:03

4

SELECT 
'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE name LIKE 'g\_str%'  ESCAPE '\'

Then review the script and run it.

You can also concatenate the result into a single string and execute with EXEC if you need an entirely automated solution.

edited May 23 '17 at 12:03

Community

1
1

answered Oct 29 '12 at 16:29

Martin Smith

438,706
87
741
845

score 3 · Answer 2 · answered Oct 29 '12 at 16:30

3

You could use dynamic SQL:

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT  @SQL = @SQL + 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME([Schema_ID])) + '.' + QUOTENAME([name]) + ';'
FROM    sys.tables
WHERE   Name LIKE 'g\_str%' ESCAPE('\');

EXECUTE SP_EXECUTESQL @SQL;

answered Oct 29 '12 at 16:30

GarethD

68,045
10
83
123

1

This will also drop tables beginning `gastro` as `_` is a wildcard character. – Martin Smith Oct 29 '12 at 16:30
1

@MartinSmith Thanks, it seems I never stop learning from you! I've corrected my answer (and only left it in because it contains the automated solution you've made reference to in your answer) – GarethD Oct 29 '12 at 16:35

score 1 · Accepted Answer · answered Oct 29 '12 at 16:33

Following query will delete tables automatically:

    BEGIN TRANSACTION

    DECLARE @tmpTablesToDelete TABLE ( 
                                        RowNumber INT PRIMARY KEY
                                       ,Query NVARCHAR(MAX)
                                     )

    INSERT INTO
            @tmpTablesToDelete 
    SELECT 
         RowNumber = ROW_NUMBER() OVER (ORDER BY (SELECT (0)))
        ,'DROP TABLE '+schemas.name+'.'+objects.name AS Query
    FROM 
        sys.objects 
    INNER JOIN
        sys.schemas
    ON
        schemas.schema_id = objects.schema_id
    WHERE 
        type = 'U' AND objects.name like 'g_str%'

    DECLARE @Counter INT
    SELECT @Counter = MAX(RowNumber) FROM @tmpTablesToDelete

    WHILE(@Counter > 0) BEGIN

        DECLARE @Query NVARCHAR(MAX)

        SELECT @Query = Query FROM @tmpTablesToDelete WHERE RowNumber = @Counter

        PRINT @Query

        EXEC sp_executesql @statement = @Query

        SET @Counter = @Counter - 1

    END

    COMMIT TRANSACTION

Drop tables that starts with specific characters

3 Answers3