/*
-- BEGIN DOC
This script populates all the foreign keys relationships into a an existing meta table
-- END DOC
*/
TRUNCATE TABLE MetaPkFkRelations
INSERT INTO MetaPkFkRelations (
[PkTable],[PKColName],[FkTable],[FkColName],[FkName],[OnDeleteCascate] , [OnUpdateCascade] )
select distinct
OBJECT_NAME (sfk.referenced_object_id) as 'PkTable' ,
COL_NAME(fc.referenced_object_id , fc.referenced_column_id) AS 'PKColName' ,
object_name(fkeyid) AS 'FkColumn',
object_name(rkeyid) as 'PkColumn',
object_name(constid),
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ) as 'OnDeleteCascade',
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' ) as 'OnUpdateCascade'
from sysforeignkeys k
INNER JOIN sys.foreign_keys sfk on k.constid = sfk.[object_id]
INNER JOIN .sys.foreign_key_columns AS fc
ON sfk.[OBJECT_ID] = fc.constraint_object_id
--SELECT
--OBJECT_NAME (f.referenced_object_id) as 'PkTable' ,
--COL_NAME(fc.referenced_object_id , fc.referenced_column_id) AS 'PKColName' ,
--OBJECT_NAME(f.parent_object_id) AS 'FkTable' ,
--COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'FkColName' ,
--f.name AS 'FkName'
--FROM .sys.foreign_keys AS f
--INNER JOIN .sys.foreign_key_columns AS fc
--ON f.OBJECT_ID = fc.constraint_object_id
SELECT TOP 5000 * FROM MetaPkFkRelations ORDER BY 1 DESC
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!