Monday, May 17, 2010

Check which tables have triggers and compare

This is useful if you have 2 or more databases in different sites.

Script to check which tables have triggers

SELECT s1.name as tablename, s2.name as triggername FROM sysobjects s1
JOIN sysobjects s2 ON
s1.id =s2.parent_obj
AND s2.xtype = 'TR'
WHERE s1.xtype = 'U'
ORDER BY s1.name


output

tablename triggername
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AccruedDivis trAccrueddivisAuditQueueInsert
AccruedDivis trAccrueddivisAuditQueueUpdate
AccruedDivis trAccrueddivisAuditQueueDelete
AlertMessages trAlertMessagesInsert
Run this on both of the servers and in the databases required
copy the data to an excel worksheet
I have two databases one in London and one in Hong Kong in this sample



use a vlookup to compare like this and the copy the formula down

=VLOOKUP(B115,'sheet.name'!B:B,1,FALSE)

No comments:

Post a Comment