問題描述

我列出了需要從 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

工作原理:

1. XML + STUFFfor string concatenation 是 SQL Server 的常用習慣用法，類似于 MySQL 中的 GROUP_CONCAT.您可以將其視為一種將多個 IF BEGIN END 塊組合成一個字符串的方法.
2. FORMATMESSAGE 將替換 %s 帶有實際表名(引用以避免 SQL 注入攻擊)
3. PRINT 用于調試以檢查生成的查詢，可以注釋
4. sp_executesql 將執行 SQL 字符串

1. XML + STUFF for string concatenation is common idiom with SQL Server, works like GROUP_CONCAT in MySQL. You can think about it as a way to combine multiple IF BEGIN END chunks into one string.
2. FORMATMESSAGE will replace %s with actual table names(quoted to avoid SQL Injection attacks)
3. PRINT is for debug to check generated query, can be commented
4. sp_executesql will execute SQL string

這篇關于生成drop table語句列表的動態sql腳本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！