Sql-Server
如何辨識呼叫特定儲存過程的所有觸發器？
我有一個問題，我需要將一些移動
stored procedures到不同的數據庫。這些SPs也是從相同的數據庫和其他數據庫執行的。特別是它們是從內部執行的
triggers。在下面的腳本中，我可以辨識從我目前所在的數據庫執行
stored procedure呼叫的所有觸發器。applicationCommission我失敗並想要實現的是：
我想
triggers在目前伺服器中找到所有呼叫它的數據庫stored procedure。我可以對下面的腳本進行哪些更改以實現此目的？要麼
我怎樣才能做到這一點？
--============================================================================ -- the trigger definition query -- marcelo miorelli -- 20-july-2017 --============================================================================= --SELECT -- QUOTENAME(SCHEMA_NAME(schema_id)) AS schema_name, -- QUOTENAME(OBJECT_NAME(parent_object_id)) AS table_name, -- QUOTENAME(so.name) AS trigger_name, -- OBJECT_DEFINITION(object_id) AS trigger_definition --FROM sys.objects so --WHERE so.type = 'TR' --=============================================================================== -- find what triggers run a stored procedure -- marcelo miorelli -- 20-july-2017 --=============================================================================== SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @SQL NVARCHAR(max) -- THE PROCEDURE THAT I AM LOOKING FOR ,@spName VARCHAR(100) = 'applicationCommission' SELECT @SQL = STUFF(( SELECT CHAR(10) + ' UNION ALL ' + CHAR(10) + ' SELECT ' + quotename(s.NAME, '''') + ' AS DB_NAME ' + CHAR(10) + ' ,QUOTENAME(OBJECT_SCHEMA_NAME(so.schema_id,db_id())) AS THE_SCHEMA ' + CHAR(10) + ' ,QUOTENAME(OBJECT_NAME(parent_object_id,db_id())) AS table_name' + CHAR(10) + ' , so.name COLLATE Latin1_General_CI_AS AS TRIGGER_NAME ' + CHAR(10) + ' ,OBJECT_DEFINITION(object_id) AS trigger_definition' + CHAR(10) + ' FROM ' + quotename(s.NAME) + '.sys.objects so ' + CHAR(10) + ' WHERE 1=1 ' + CHAR(10) + --' AND s.name LIKE @spName ' + CHAR(10) + ' AND so.[type] = ''TR''' FROM sys.databases s WHERE 1=1 AND S.state = 0 AND s.name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution') ORDER BY s.NAME FOR XML PATH('') ,TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 11, '') --PRINT @SQL IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL BEGIN DROP TABLE #RADHE END CREATE TABLE #Radhe( [database_name] sysname NOT NULL, [schema_name] sysname NULL, [table_name] sysname NULL, [trigger_name] sysname NOT NULL, [trigger_definition] NVARCHAR(MAX) ); INSERT INTO #Radhe EXECUTE sp_executeSQL @SQL --,N'@spName varchar(100)' --,@spName SELECT * FROM #Radhe WHERE trigger_definition LIKE '%' + @spName + '%'編輯：
這就是我結束這個案子的方式：
--============================================================================================== -- version using cursor -- in all databases - get all triggers that fire a particular stored procedure (accept wildcards %) -- marcelo miorelli -- 27-july-2017 -- https://stackoverflow.com/questions/1409965/t-sql-a-proper-way-to-close-deallocate-cursor-in-the-update-trigger -- https://stackoverflow.com/questions/21225324/open-and-close-cursors-inside-or-outside-a-transaction-and-how-to-close-a-cursor --============================================================================================== USE master; GO IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL BEGIN DROP TABLE #Radhe END CREATE TABLE #Radhe ( [DB] NVARCHAR(128) NULL, [THE_SCHEMA] NVARCHAR(258) NULL, [table_name] NVARCHAR(258) NULL, [TRIGGER_NAME] NVARCHAR(128) NOT NULL, [trigger_definition] NVARCHAR(max) NULL ) DECLARE @name sysname; DECLARE @SQL nvarchar(max); DECLARE @theSQL nvarchar(max); DECLARE @spName VARCHAR(100) = '%PropInsert%' -- THE PROCEDURE THAT I AM LOOKING FOR SELECT @SQL = '------------------------------------------------------------------------------------------------------------------- SELECT DB = DB_NAME() ,QUOTENAME(OBJECT_SCHEMA_NAME(so.schema_id,db_id())) AS THE_SCHEMA ,QUOTENAME(OBJECT_NAME(parent_object_id,db_id())) AS table_name , so.name COLLATE Latin1_General_CI_AS AS TRIGGER_NAME ,OBJECT_DEFINITION(object_id) AS trigger_definition FROM sys.objects so WHERE 1=1 AND so.[type] = ''TR'' -- get only the triggers -------------------------------------------------------------------------------------------------------------------'; -- PRINT @SQL BEGIN TRY DECLARE THE_DBS CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR SELECT s.name FROM sys.databases s WHERE 1=1 AND s.state = 0 AND s.name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution') AND s.name NOT LIKE 'ReportServer%' OPEN THE_DBS; FETCH NEXT FROM THE_DBS INTO @name; WHILE @@FETCH_STATUS = 0 BEGIN SET @theSQL = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @SQL' + CHAR(10) + ',N''@spName varchar(100)'',' + CHAR(10) + '''' + @spName + '''' +';' + CHAR(10) ------------------------------------------- --do the insert here ------------------------------------------- INSERT INTO #Radhe([DB],[THE_SCHEMA],[table_name],[TRIGGER_NAME],[trigger_definition] ) EXEC sys.sp_executesql @theSQL , N'@SQL nvarchar(max)' , @SQL FETCH NEXT FROM THE_DBS INTO @name; END ------------------------------------------- BEGIN TRY --clean it up CLOSE THE_DBS; DEALLOCATE THE_DBS; END TRY BEGIN CATCH --do nothing END CATCH ------------------------------------------- END TRY BEGIN CATCH ------------------------------------------- BEGIN TRY --clean it up CLOSE THE_DBS; DEALLOCATE THE_DBS; END TRY BEGIN CATCH --do nothing END CATCH ------------------------------------------- DECLARE @ERRORMESSAGE NVARCHAR(512), @ERRORSEVERITY INT, @ERRORNUMBER INT, @ERRORSTATE INT, @ERRORPROCEDURE SYSNAME, @ERRORLINE INT, @XASTATE INT SELECT @ERRORMESSAGE = ERROR_MESSAGE(), @ERRORSEVERITY = ERROR_SEVERITY(), @ERRORNUMBER = ERROR_NUMBER(), @ERRORSTATE = ERROR_STATE(), @ERRORPROCEDURE = ERROR_PROCEDURE(), @ERRORLINE = ERROR_LINE() SET @ERRORMESSAGE = ( SELECT CHAR(13) + 'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) + 'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) + 'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) + 'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) + 'Routine_Name:' + SPACE(1) + COALESCE(@ErrorProcedure,'') + SPACE(1) + CHAR(13) + 'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) + 'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) + 'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) + 'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13) ) --We can also save the error details to a table for later reference here. RAISERROR (@ERRORMESSAGE,16,1) END CATCH -------------------------------- -- by now the #Radhe table has been populated -------------------------------- SELECT * FROM #Radhe WHERE trigger_definition LIKE @spName
Marcello，如果您絕對必須能夠使用單個腳本來實現您的目標，您可以嘗試遍歷數據庫並在每個數據庫的上下文中執行 TSQL。下面是一個範例，其中 sp_executesql 在游標循環中從/在每個數據庫中執行 sp_executesql。請注意，@Tsql 在這裡是靜態的，因此它是在游標循環之外聲明的。然而，它可以很容易地在循環中動態建構。這種方法並不容易。它甚至可能看起來有點令人費解。我會讓你做法官。
/* Build the TSQL statement that you want to run on each database. Here is an example that finds database users that are members of specific fixed database roles and drops them from those roles. Note the single quotes are quadrupled. */ DECLARE @Tsql NVARCHAR(MAX) = ' DECLARE @User SYSNAME, @Role SYSNAME DECLARE curUsers CURSOR FAST_FORWARD READ_ONLY FOR SELECT u.name UserName, r.name dbRole FROM sys.database_principals u JOIN sys.database_role_members rm ON rm.member_principal_id = u.principal_id JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id WHERE r.name IN (''''db_accessadmin'''', ''''db_backupoperator'''', ''''db_securityadmin'''') AND u.name NOT IN (''''dbo'''', ''''guest'''', ''''INFORMATION_SCHEMA'''', ''''public'''', ''''sys'''') ORDER BY u.name, r.name OPEN curUsers FETCH NEXT FROM curUsers INTO @User, @Role WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_droprolemember @Role, @User FETCH NEXT FROM curUsers INTO @User, @Role END CLOSE curUsers DEALLOCATE curUsers '; DECLARE @DB SYSNAME DECLARE curDB CURSOR FOR SELECT d.name FROM master.sys.databases d --Comment out/adjust this WHERE clause as needed. WHERE d.name NOT IN ('master','tempdb') OPEN curDB FETCH NEXT FROM curDB INTO @DB, @DBOwner WHILE @@FETCH_STATUS = 0 BEGIN PRINT @DB --Execute sp_executesql, which executes sp_executesql --in the context of a specific database. SET @Tsql = '[' + @DB + ']..sp_executesql N''' + @Tsql + '''' EXEC sp_executesql @Tsql; FETCH NEXT FROM curDB INTO @DB, @DBOwner END CLOSE curDB DEALLOCATE curDB GO