December 18, 2009 at 3:43 am
Hi,
on http://www.sqlservercentral.com/scripts/Primary+key/68346/%5B/url%5D "Jason S Wong" described a way to find all tables which references to a specific table via foreign keys. This is useful for me, but I need another thing too. I'm writing a script for database and table backup/restore. So I have to consider all foreign keys and triggers when manipulation a table. When deleting and re-creating a table with my restore script it is possible to lost data, when a table references on the deleted table.
So I thought, it is a good way to find all tables with foreign keys or triggers, remember the create script for these, delete them, doing my restore and then re-create the foreign keys and triggers.
So I need a comparable script for checking on referring triggers!?
Are there any other constraints I have to consider?
Regards
December 18, 2009 at 10:35 am
In sys.objects the type is 'TR'
You would be able to pull a list of triggers that way.
Or you could query sys.triggers and get a list of the triggers and be able to map them to the parent object by using the parent_id column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 10:53 am
here's an example of what Jason was talking about; my SandBox database had a database trigger, and I thought that deserved mentioning and a demo of special handling:
SELECT
CASE
WHEN object_name(parent_id) IS NULL Then 'Database'
ELSE object_name(parent_id)
END As TriggerOwner,
name AS TriggerName
from sys.triggers
--results
TriggerOwner TriggerName
Database ReturnPREventData
Lowell
December 18, 2009 at 11:00 am
I know the existence of sys.triggers. But for me it's interesting what the trigger does respectively what other tables a trigger touches.
December 18, 2009 at 11:14 am
still fairly easy: you jsut add the dependancies and the columns to teh same query:
SELECT
CASE
WHEN object_name(tr.parent_id) IS NULL Then 'Database'
ELSE object_name(tr.parent_id)
END As TriggerOwner,
tr.name AS TriggerName ,
object_name(sd.depid) As DependantTable,
col.name as DependantColumn,
sd.depnumber as ColOrder
from sys.triggers tr
left outer join sysdepends sd on tr.object_id = sd.id
left outer join syscolumns col on sd.depid = col.id and sd.depnumber = col.colid
--results
TriggerOwner TriggerName DependantTable DependantColumn ColOrder
Database ReturnPREventData DDLEventLog EventDate 1
Database ReturnPREventData DDLEventLog UserName 2
Database ReturnPREventData DDLEventLog objectName 3
Database ReturnPREventData DDLEventLog CommandText 4
Database ReturnPREventData DDLEventLog EventType 5
Lowell
December 18, 2009 at 11:20 am
That sounds great, I'll test this and give a feedback.
December 18, 2009 at 12:30 pm
Lowell (12/18/2009)
still fairly easy: you jsut add the dependancies and the columns to teh same query:
SELECT
CASE
WHEN object_name(tr.parent_id) IS NULL Then 'Database'
ELSE object_name(tr.parent_id)
END As TriggerOwner,
tr.name AS TriggerName ,
object_name(sd.depid) As DependantTable,
col.name as DependantColumn,
sd.depnumber as ColOrder
from sys.triggers tr
left outer join sysdepends sd on tr.object_id = sd.id
left outer join syscolumns col on sd.depid = col.id and sd.depnumber = col.colid
--results
TriggerOwner TriggerName DependantTable DependantColumn ColOrder
Database ReturnPREventData DDLEventLog EventDate 1
Database ReturnPREventData DDLEventLog UserName 2
Database ReturnPREventData DDLEventLog objectName 3
Database ReturnPREventData DDLEventLog CommandText 4
Database ReturnPREventData DDLEventLog EventType 5
Geez, Lowell - spoiled all my fun.:cool:
Nice script.
Now, just to play advocate - what if you want the hierarchy as it relates to one specific table? (i.e. triggera fires and then triggerb fires and then triggerd fires - but triggerc may not need to fire due to the table relationships).:hehe:
I'm sure this question is headed in that direction anyway.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2010 at 3:05 am
I'm back. Great, the trigger script fits me needs.
@jason: the hierarchy would be very interesting too, but for me it's only interesting, if there is a trigger which relates to my table I want to delete. (It would be enough to go through the dependent tables and look for other triggers)
I wrote about "Jason S Wong's" foreign key script. I forgot, that I need the dependent columns for parent table and fk-table too. But there are no depencies in sysdepends. Is there a way to get this information?
What I want to do in SQL is more a dirty hack. I wrote that I want to restore a single table. Now, if there exist a foreign key constraint, I want to temporary delete it, restore the table, check for consistency and re-create the foreign key constraint. If there is any inconsistency the user has two options. One is to accept the loss of data in the dependent tables. The second is to let the tool restore all dependent tables from the backup, too. This is not a clean solution, but I think this is the only way, is it?
Cheers, Nico
January 6, 2010 at 4:48 am
Ha, there is the system table foreign_key_columns. I will test a little bit.
January 6, 2010 at 5:34 am
SELECT c.name AS FK,e.constraint_column_id AS FKc,d.name AS P,f.name AS Pcol,g.name AS Ptype,b.name AS C,h.name AS Ccol,i.name AS Ctype
FROM sys.foreign_keys a
INNER JOIN sys.objects b ON b.object_id = a.referenced_object_id
INNER JOIN sys.objects c ON a.object_id = c.object_id
INNER JOIN sys.objects d ON a.parent_object_id = d.object_id
INNER JOIN sys.foreign_key_columns e ON a.object_id=e.constraint_object_id
INNER JOIN sys.syscolumns f ON e.parent_object_id=f.id AND e.parent_column_id=f.colid
INNER JOIN sys.systypes g ON f.xusertype=g.xusertype
INNER JOIN sys.syscolumns h ON e.referenced_object_id=h.id AND e.referenced_column_id=h.colid
INNER JOIN sys.systypes i ON h.xusertype=i.xusertype
WHERE b.name=XXX
ORDER BY a.object_id,e.constraint_column_id
is what I need 😉
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply