SQL to delete tables that begins with common prefix

Question

I am looking to write a SQL query/stored procedure which would delete tables within a DB that begin with a common prefix like "table".

However the names of the tables begin with the prefix following by a number and I do not want to delete them all, if I could append the table numbers to the prefix.

Thanks

I don't want to delete every single table I have which contains the prefix, as there are hundreds of them, I want to delete the tables which contain the prefix and the numbers appended — user3296793, Mar 22 '19 at 10:35
Is there a rule to establish which to delete or which to keep? — P.Salmon, Mar 22 '19 at 11:00

score 1 · Answer 1 · answered Mar 22 '19 at 09:46

1

look at this: Get table names using SELECT statement in MySQL

you could join that table with constraints on the table prefix

hope that helps and have fun

answered Mar 22 '19 at 09:46

Patrick Schneider

68
1
8

BossRoss · Answer 2 · 2019-03-22T11:18:32.350

Find all tables where column like:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%SubId%'
ORDER BY    TableName
            ,ColumnName;

Find all tables where table name like:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%tbl%'
ORDER BY    TableName
            ,ColumnName;

Find all tables in a specific schema:

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'cmc';

Once you have the tables you need to delete you can just write delete statements for each one. Or you could use something like the below:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName',
            'drop table ' + t.name
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%tbl%'
ORDER BY    TableName
            ,ColumnName;

EDIT

Below a select with a little more detail on the where clause:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'          
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       
            t.name LIKE 'tbl%' -- where the table name starts with the letters 'tbl'
            OR t.name LIKE 'tbl%123%' -- where the table name starts with the letters 'tbl' and has the numbers '123' in the table name
            OR c.name LIKE '%colName%' -- where a column has a name that contains the letters 'colName'

ORDER BY    TableName
            ,ColumnName;

How would these work if I wanted to find the table names using this however filtering the tables which have an ID that matches one I specify found within one of the columns as well — user3296793, Mar 22 '19 at 10:33
I am not sure if I understand you completely. If the edit to my answer does not help you then please clarify your question but adding some more information and examples of what you are trying to do. — BossRoss, Mar 22 '19 at 11:19

score 1 · Accepted Answer · answered Mar 22 '19 at 15:48

Find all tables names that start with your prefix and create drop query dynamically and insert result into temp table like this :

DECLARE @YourPrefix VARCHAR(MAX) = 'table'
SELECT    
    'DROP TABLE ' + t.name AS DropQuery
INTO #TMP
FROM        
      sys.tables  t   
WHERE
    t.name LIKE @YourPrefix + '%'

Write cursor on temp table and use sp_executesql to execute drop query like this.

DECLARE @Query NVARCHAR(MAX) = NULL
DECLARE C CURSOR FOR
    SELECT 
        T.DropQuery 
    FROM 
        #TMP T
OPEN C
WHILE 1=1
BEGIN
    FETCH NEXT FROM C INTO @Query
    IF @@FETCH_STATUS <> 0 BREAK;
    EXEC sp_executesql @Query
END
CLOSE C
DEALLOCATE C

It,s drop all table that start with your prefix.

SQL to delete tables that begins with common prefix

3 Answers3