sql - 生成基于外键约束自动连接表的选择查询的 SQL 选择？

Question

是否可以编写一个 SQL select 语句，生成一个select基于外键约束自动连接多个表的 SQL 语句？

我对 SQL Server 数据库特别感兴趣。

我已经看到并使用了查询sys.foreign_keys和sys.tables. 我尝试在SELECT此处添加语句的公式，但我知道这不适用于多列键表。

例如

select 
    schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
    '>-' as rel,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
    substring(column_names, 1, len(column_names)-1) as [fk_columns],
    substring(ref_column_names, 1, len(ref_column_names)-1) as [ref_columns],
    fk.name as fk_constraint_name,
    'SELECT * from ' + schema_name(pk_tab.schema_id) + '.' + pk_tab.name + ' A '
    + ' JOIN ' + schema_name(fk_tab.schema_id) + '.' + fk_tab.name + ' B '
    + ' ON ' + 'A.' + substring(ref_column_names, 1, len(ref_column_names)-1)
    + ' = ' + 'B.' + substring(column_names, 1, len(column_names)-1)
     AS SelectStmnt
from 
    sys.foreign_keys fk
inner join 
    sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id
inner join 
    sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id
cross apply 
    (select col.[name] + ', '
     from sys.foreign_key_columns fk_c
     inner join sys.columns col on fk_c.parent_object_id = col.object_id
                                and fk_c.parent_column_id = col.column_id
     where fk_c.parent_object_id = fk_tab.object_id
       and fk_c.constraint_object_id = fk.object_id
     order by fk_c.constraint_column_id
     for xml path ('') ) D (column_names)
cross apply 
    (select col.[name] + ', '
     from sys.foreign_key_columns fk_c
     inner join sys.columns col on fk_c.referenced_object_id = col.object_id
                                and fk_c.referenced_column_id = col.column_id
     where fk_c.referenced_object_id = pk_tab.object_id
       and fk_c.constraint_object_id = fk.object_id
     order by fk_c.constraint_column_id
     for xml path ('') ) E (ref_column_names)
order by 
    schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name

我对自动生成 SQL 选择语句感兴趣，该语句连接给定 2 个感兴趣的表名的表。

示例表可能是：

CREATE TABLE [dbo].[Project]
(
        [Project_Code] [int] NOT NULL,
        [Project_Name] [nvarchar](max) NULL,
        [Department] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Project_Code] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
    
CREATE TABLE [dbo].[Employee]
(
        [Emp_Id] [int] NOT NULL,
        [Emp_Name] [nvarchar](max) NULL,
        [Manager_Id] [int] NULL,
        [Project_Id] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Emp_Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
    
ALTER TABLE [dbo].[Employee]  WITH CHECK 
    ADD CONSTRAINT [IFManagerHasEmployees] 
        FOREIGN KEY([Manager_Id]) REFERENCES [dbo].[Employee] ([Emp_Id])
GO
    
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [IFManagerHasEmployees]
GO
    
ALTER TABLE [dbo].[Employee] WITH CHECK 
    ADD CONSTRAINT [IFProjectHasEmployees] 
        FOREIGN KEY([Project_Id]) REFERENCES [dbo].[Project] ([Project_Code])
GO
    
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [IFProjectHasEmployees]
GO

CREATE TABLE [dbo].[Bands]
(
    [ThtrBandID] [char](2) NOT NULL,
    [ThtrBandIssuerID] [char](1) NOT NULL,
    [Description] [varchar](30) NOT NULL,
    [AuditTime] [DateTime] NULL,
    [Priority] [tinyint] NOT NULL,
    [ConcurrencyToken] [timestamp] NOT NULL,
    [TheatreBandsIdentity] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [XPKBands] PRIMARY KEY CLUSTERED 
(
    [TheatreBandsIdentity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [XAK1Bands] UNIQUE NONCLUSTERED 
(
    [ThtrBandIssuerID] ASC,
    [ThtrBandID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Items]
(
    [SvcClass] [char](1) NOT NULL,
    [ProgramID] INT NOT NULL,
    [MBACNo] [smallint] NOT NULL,
    [EffDate] [smalldatetime] NOT NULL,
    [AuditTime]  [DateTime] NULL,
    [NatThtrBandIssuerID] [char](1) NULL,
    [PatClass] [char](2) NULL,
    [NatThtrBandID] [char](2) NULL,
    [AHSAItemIdentity] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [XPKAHSAItems] PRIMARY KEY CLUSTERED 
(
    [AHSAItemIdentity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) 
ON [PRIMARY],
) ON [PRIMARY]


ALTER TABLE [dbo].[Items] WITH CHECK 
    ADD CONSTRAINT [IFTheatreBanding] 
        FOREIGN KEY([NatThtrBandIssuerID], [NatThtrBandID])
                REFERENCES [dbo].[Bands] ([ThtrBandIssuerID], [ThtrBandID])
GO

我尝试的 SQL 有点工作..

回报：

SELECT * from dbo.Employee A  JOIN dbo.Employee B  ON A.Emp_Id = B.Manager_Id

SELECT * from dbo.Project A  JOIN dbo.Employee B  ON A.Project_Code = B.Project_Id

SELECT * from dbo.Bands A  JOIN dbo.Items B  ON A.ThtrBandID, ThtrBandIssuerID = B.NatThtrBandIssuerID, NatThtrBandID

最后一个不正确......应该是：

SELECT * from dbo.Bands A JOIN dbo.Items B ON A.[ThtrBandID] = B.[NatThtrBandID] AND A.[ThtrBandIssuerID] = B.[NatThtrBandIssuerID]

标签： sqlsql-server