SQL to delete tables that begins with common prefix

问题: 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 beg...

问题:

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


回答1:

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.


回答2:

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


回答3:

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;
  • 发表于 2019-03-27 01:30
  • 阅读 ( 203 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除