仅使用脚本向导为触发器生成脚本 [英] Generate script for triggers only using script wizard

问题描述

我有 SQL Server 2008 R2.我在一个数据库中有大约 150 个表，并且我最近为每个表创建了触发器.它在我的本地环境中运行良好.

I have SQL Server 2008 R2. I have around 150 tables in a database and for each table I have recently created triggers. It is working fine in my local environment.

现在我想将它们部署到我的实时环境中.问题是我只想部署触发器.我尝试了 Generate Script Wizard 但它正在创建带有表模式和触发器的脚本，而不仅仅是触发器.

Now I want to deploy them on my live environment. The question is I want to deploy only the triggers. I tried the Generate Script wizard but it is creating script with table schema along with triggers, NOT triggers only.

无论如何要生成所有触发器删除并创建类型脚本?

Is there anyway to generate all the triggers drop and create type script?

推荐答案

忘记向导.我认为你必须用代码弄脏你的手.下面的脚本打印所有触发器代码并将其存储到表中.只需复制脚本的打印输出或从#triggerFullText 获取它.

Forget the wizard. I think you have to get your hands dirty with code. Script below prints all triggers code and stores it into table. Just copy the script's print output or get it from #triggerFullText.

USE YourDatabaseName
GO
SET NOCOUNT ON;

CREATE TABLE #triggerFullText ([TriggerName] VARCHAR(500), [Text] VARCHAR(MAX))
CREATE TABLE #triggerLines ([Text] VARCHAR(MAX))

DECLARE @triggerName VARCHAR(500)
DECLARE @fullText VARCHAR(MAX)

SELECT @triggerName = MIN(name)
FROM sys.triggers

WHILE @triggerName IS NOT NULL
BEGIN
    INSERT INTO #triggerLines 
    EXEC sp_helptext @triggerName

    --sp_helptext gives us one row per trigger line
    --here we join lines into one variable
    SELECT @fullText = ISNULL(@fullText, '') + CHAR(10) + [TEXT]
    FROM #triggerLines

    --adding "GO" for ease of copy paste execution
    SET @fullText = @fullText + CHAR(10) + 'GO' + CHAR(10)

    PRINT @fullText

    --accumulating result for future manipulations
    INSERT INTO #triggerFullText([TriggerName], [Text])
    VALUES(@triggerName, @fullText)

    --iterating over next trigger
    SELECT @triggerName = MIN(name)
    FROM sys.triggers
    WHERE name > @triggerName

    SET @fullText = NULL

    TRUNCATE TABLE #triggerLines
END

DROP TABLE #triggerFullText
DROP TABLE #triggerLines

这篇关于仅使用脚本向导为触发器生成脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！