问题:
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;