Tuesday, January 14, 2014

Making Relationship Easy to Find (in SQL Server)

declare @tablename nvarchar(100)
set @tablename = '%mytablename%'

;with
TableRelations as
(
   SELECT f.name AS ForeignKey,
      SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
      OBJECT_NAME(f.parent_object_id) AS TableName,
      COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
      SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
      OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
      COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
   FROM sys.foreign_keys AS f
      INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
      INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

--where OBJECT_NAME (f.referenced_object_id) like '%sometablename%'
--where OBJECT_NAME(f.parent_object_id) like '%sometablename%'
 
)

 
select * from TableRelations
where
TableName like @tablename
or ReferenceTableName like @tablename
 

Props to Pinal Dave

No comments: