如何列出(或导出)数据库中所有触发器的代码？

Question

我正在将数据库中的本地时间更改为UTC-time。

有许多触发器可以将信息复制到当前使用GETDATE()的历史表中。

我想在数据库中找到每个使用GETDATE() (而不是GETUTCDATE())的触发器，有没有办法自动做到这一点？

我已经按select * from sys.triggers列出了它们，但是为了能够找到GETDATE()的用法，我还需要查看实际的代码。

Answer 1

您可以尝试以下操作：

SELECT      o.[name],
            c.[text]
FROM        sys.objects AS o
INNER JOIN  sys.syscomments AS c
ON      o.object_id = c.id
WHERE   o.[type] = 'TR'

Answer 2

下面是我用来导出触发器的脚本：

DECLARE @t VARCHAR (MAX)
SET @t = ''
SELECT @t = @t + 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + s.name + '.' + o.name +'''))
DROP TRIGGER ' + s.name + '.' + o.name + '
GO
' + OBJECT_DEFINITION (OBJECT_ID( s.name + '.' + o.name )) +'
GO
'
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.objects o2 ON o.parent_object_id = o2.object_id
WHERE o. [type] = 'TR'
AND (
OBJECTPROPERTY ( o.object_id , 'ExecIsInsertTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsUpdateTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsDeleteTrigger' ) = 1
)
SELECT @t AS [processing-instruction(x)] FOR XML PATH ('')

如果对任何人都没有意义，请在此处查看更多详细信息：

http://paulfentonsql.co.uk/2015/09/01/generate-createdrop-statements-for-all-triggers-of-a-given-type/

Answer 3

如果要从数据库中导出所有触发器...下面是一些代码：

DECLARE @vchServerName  VARCHAR(500)
DECLARE @vchDBName      VARCHAR(500)

DECLARE @intLoop        INTEGER
DECLARE @intTotalRows   INTEGER

DECLARE @intId          INTEGER
DECLARE @vchName        VARCHAR(500)
DECLARE @vchSQL         VARCHAR(4000)

-- supress count (just so log looks nicer!)
SET NOCOUNT ON

-- get current DB and server 
SET @vchDBName = DB_NAME()
SET @vchServerName = @@servername

-- get list of XXX
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id ) fldRowNum 
    ,o.object_id    fldId
    ,s.name + '.' + o.name          fldName
INTO #tblFound
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE [type] = 'TR'
SET @intTotalRows   = @@ROWCOUNT
SET @intLoop    = 1


-- loop thru list
WHILE @intLoop <= @intTotalRows 
BEGIN
    SELECT @intID = fldId
        ,@vchName = fldName
    FROM    #tblFound
    WHERE   fldRowNum = @intLoop

    PRINT 'Exporting ' + @vchName + '...'

    -- NOTE: I'm using a version of bcp that doens't have -D parameter so I need to use DB name here
    SET @vchSQL = 'SELECT c.[text] FROM ' + @vchDBName + '.sys.syscomments AS c WHERE c.id = ' + CONVERT(VARCHAR,@intID)
    SET @vchSQL = 'bcp "' + @vchSQL + '" queryout "c:\temp\' + @vchName + '.sql"  -c -t -T -S ' + @vchServerName

    EXEC master..XP_CMDSHELL @vchSQL

    SET @intLoop = @intLoop + 1
END 

DROP TABLE #tblFound

PRINT 'Done'