问题描述
我列出了需要从 SQL Server 中删除的表(大约 100++).以下是我将使用的示例代码
I've list of tables (around 100++) that need to be dropped from SQL Server. Below is the sample code that I would use
IF OBJECT_ID('dbo.DS_Area_TBL', 'U') IS NOT NULL
drop table dbo.DS_Area_TBL
Print 'dbo.DS_Area_TBL has been dropped'
我需要用其他表名替换表名 100++ 时间.如何编写一个可以自动生成查询列表的动态sql脚本?
I need to replace table name 100++ time with other table name. How to write a dynamic sql script that can auto generate list of queries?
推荐答案
你可以先生成脚本然后用动态 sql 执行:
You could first generate script then execute with dynamic sql:
CREATE TABLE a(a INT);
CREATE TABLE b(a INT);
CREATE TABLE c(a INT);
CREATE TABLE d(a INT);
CREATE TABLE e(a INT);
CREATE TABLE tab(tab_name SYSNAME); -- here are table names stored
INSERT INTO tab VALUES ('a'),('b'),('c'),('d'),('e');
-- main part
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STUFF((SELECT ' ' + FORMATMESSAGE(
'IF OBJECT_ID(''%s'', ''U'') IS NOT NULL
BEGIN
DROP TABLE %s;
PRINT ''%s has been dropped '';
END
', QUOTENAME(tab_name),QUOTENAME(tab_name),QUOTENAME(tab_name))
FROM tab
FOR XML PATH('')), 1, 1, '');
PRINT @sql; -- for debug
EXEC [dbo].[sp_executesql]
@sql;
如果你使用的SQL Server
版本低于2012你需要用字符串连接+
来改变FORMATMESSAGE
.
If you use version of SQL Server
lower than 2012 you need to change FORMATMESSAGE
with string concatenation +
.
您可以通过修改模板轻松地使用自定义架构等扩展此脚本:
You could easily extend this script with custom schema and so on by modifying template:
'IF OBJECT_ID(''%s'', ''U'') IS NOT NULL
BEGIN
DROP TABLE %s;
PRINT ''%s has been dropped '';
END
'
输出:
IF OBJECT_ID('[a]', 'U') IS NOT NULL
BEGIN
DROP TABLE [a];
PRINT '[a] has been dropped ';
END
IF OBJECT_ID('[b]', 'U') IS NOT NULL
BEGIN
DROP TABLE [b];
PRINT '[b] has been dropped ';
END
IF OBJECT_ID('[c]', 'U') IS NOT NULL
BEGIN
DROP TABLE [c];
PRINT '[c] has been dropped ';
END
IF OBJECT_ID('[d]', 'U') IS NOT NULL
BEGIN
DROP TABLE [d];
PRINT '[d] has been dropped ';
END
IF OBJECT_ID('[e]', 'U') IS NOT NULL
BEGIN
DROP TABLE [e];
PRINT '[e] has been dropped ';
END
工作原理:
- XML + STUFFfor string concatenation 是 SQL Server 的常用习惯用法,类似于
MySQL
中的GROUP_CONCAT
.您可以将其视为一种将多个IF BEGIN END
块组合成一个字符串的方法. FORMATMESSAGE
将替换%s
带有实际表名(引用以避免 SQL 注入攻击)PRINT
用于调试以检查生成的查询,可以注释sp_executesql
将执行 SQL 字符串
- XML + STUFF for string concatenation is common idiom with SQL Server, works like
GROUP_CONCAT
inMySQL
. You can think about it as a way to combine multipleIF BEGIN END
chunks into one string. FORMATMESSAGE
will replace%s
with actual table names(quoted to avoid SQL Injection attacks)PRINT
is for debug to check generated query, can be commentedsp_executesql
will execute SQL string
这篇关于生成drop table语句列表的动态sql脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!