Finding all tables and fields on "many" side of relations with a certain table
in Sql Server 2005, I have a master table, and several other tables which are related to this master through several one to many relations. How can I find all tables and fields which are in relation with the primary key in the master table, on "many" side?
I know I can extract t开发者_运维知识库his by querying views from INFORMATION_SCHEMA, but I don't know where exactly I can find this info.
Thank you
Check out:
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
I found the answer with some help on sql server groups I use the following query, which returns me schema name, table and field name on one and many side of the relations:
SELECT
SchemaParent.name AS ParentSchemaName,
TableParent.name AS ParentTableName,
ColumnParent.name AS ParentColumnName,
SchemaChild.name AS ChildSchemaName,
TableChild.name AS ChildTableName,
ColumnChild.name AS ChildColumnName
FROM
sys.foreign_key_columns AS kc INNER JOIN
sys.objects AS TableChild ON kc.parent_object_id = TableChild.object_id INNER JOIN
sys.schemas AS SchemaChild ON TableChild.schema_id = SchemaChild.schema_id INNER JOIN
sys.objects AS TableParent ON kc.referenced_object_id = TableParent.object_id INNER JOIN
sys.schemas AS SchemaParent ON TableParent.schema_id = SchemaParent.schema_id INNER JOIN
sys.columns AS ColumnParent ON kc.referenced_object_id = ColumnParent.object_id AND kc.referenced_column_id = ColumnParent.column_id INNER JOIN
sys.columns AS ColumnChild ON kc.parent_object_id = ColumnChild.object_id AND kc.parent_column_id = ColumnChild.column_id
ORDER BY ParentTableName, ChildTableName
精彩评论