October 3, 2005 at 9:18 am
Hi
I wanted to put a simple query in a proc and set a daily job that will tell me if any trigger is disabled.
Following query can help me with this.
SELECT name, objectproperty(object_id(name),'execistriggerdisabled')
FROM sysobjects
WHERE type = 'TR'
AND objectproperty(object_id(name),'execistriggerdisabled') = 1
But I wanted to keep procedure with above query in a central database (dbadmin or whatever) and wanted to run it from there for various databases on that server by somehow passing the database name.
I think I can run a query like "....FROM DBNAME.SYSOBJECTS" format but "Objectproperty" doesn't work when I have the procedure in one database and want to run the query for some other database.
In short, say I have a procedure with above query in some "dbadmin or xyz" database can I run it from there to see if any trigger is disabled in "Northwind or Pubs or ...whatever" database without moving that procedure to every single database on that server.
Thanks for all the help.
October 3, 2005 at 9:31 am
This will get you going :
CREATE TABLE dbo.Test (id int not null primary key clustered)
GO
CREATE TRIGGER dbo.tr_test_IUD ON dbo.Test
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
ROLLBACK TRANSACTION
RAISERROR ('This is just a test table', 13, 1)
SET NOCOUNT OFF
GO
ALTER TABLE dbo.Test DISABLE TRIGGER tr_test_IUD
GO
EXEC SP_MSForEachDB 'USE ? SELECT ''?'' AS [?], name AS TriggerName, objectproperty(object_id(name),''execistriggerdisabled'') AS TriggerIsDisabled FROM ?.dbo.sysobjects WHERE type = ''TR'' AND objectproperty(object_id(name),''execistriggerdisabled'') = 1'
GO
DROP TABLE dbo.Test
BTW I think you'd be better inserting into a global table, then selecting from it at the end.
October 3, 2005 at 9:53 am
Thank you so very much for your help RGR I really appreciate it.
Regards
October 3, 2005 at 9:59 am
HTH.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply