探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
接着上篇文章《解析SQL 表结构信息查询 含主外键、自增长》里面提到了INFORMATION_SCHEMA视图，其实到了SQL 2005微软都主推大家使用INFORMATION_SCHEMA系统视图，而不是在使用sys东东了，当然目前还是有许多信息只能通过sys视图来查询。这里我们还是以查询表结果信息为例来说明一些主要的INFORMATION_SCHEMA视图的使用。
首先我们需要查询列的信息，这需要用到[INFORMATION_SCHEMA].[COLUMNS]系统视图来查询数据列的信息，SQL 如下：
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
c.DATA_TYPE ,
c.CHARACTER_MAXIMUM_LENGTH ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.NUMERIC_PRECISION ,
c.NUMERIC_SCALE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = \'Address\'
运行结果如下：
我们都知道我们在定义列的时候一般的使用都是varchar(50)之类的信息，这里我们需要整合DATA_TYPE和CHARACTER_MAXIMUM_LENGTH信息，当CHARACTER_MAXIMUM_LENGTH为-1时即使说没有指定具体最大长度，数据的指定长度信息是max，而numeric需要整合NUMERIC_PRECISION、NUMERIC_SCALE信息。修改后的SQL如下：
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
现在我们需要标记这张表的那些列是主键，那些列是外键，要查询表的主、外键信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系统视图
运行结果如图：
修改我们先前的SQL语句：
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_FOREIGN_KEY
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
现在我们已经知道那些列是主键那些是外键，接下来的就是外键列所关联的外检表信息，这里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系统视图，运行该视图如下：
所以修改我们的SQL如下：
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_FOREIGN_KEY,
fkcu.COLUMN_NAME AS FOREIGN_KEY,
fkcu.TABLE_NAME AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
我们的查询结果中显示了太多的NULL，看着不怎么舒服，还有我们的表名应该显示一次就可以，修改SQL如下：
SELECT CASE WHEN c.ORDINAL_POSITION = 1
THEN c.TABLE_SCHEMA + \'.\' + c.TABLE_NAME
ELSE \'\'
END AS TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
ISNULL(c.COLUMN_DEFAULT, \'\') AS COLUMN_DEFAULT ,
CASE WHEN c.IS_NULLABLE = \'YES\' THEN \'√\'
ELSE \'\'
END IS_NULLABLE ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'√\'
ELSE \'\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'√\'
ELSE \'\'
END AS IS_FOREIGN_KEY ,
ISNULL(fkcu.COLUMN_NAME, \'\') AS FOREIGN_KEY ,
ISNULL(fkcu.TABLE_NAME, \'\') AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
有不对的地方还请大家拍砖！
首先我们需要查询列的信息，这需要用到[INFORMATION_SCHEMA].[COLUMNS]系统视图来查询数据列的信息，SQL 如下：
复制代码 代码如下:
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
c.DATA_TYPE ,
c.CHARACTER_MAXIMUM_LENGTH ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.NUMERIC_PRECISION ,
c.NUMERIC_SCALE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = \'Address\'
运行结果如下：
我们都知道我们在定义列的时候一般的使用都是varchar(50)之类的信息，这里我们需要整合DATA_TYPE和CHARACTER_MAXIMUM_LENGTH信息，当CHARACTER_MAXIMUM_LENGTH为-1时即使说没有指定具体最大长度，数据的指定长度信息是max，而numeric需要整合NUMERIC_PRECISION、NUMERIC_SCALE信息。修改后的SQL如下：
复制代码 代码如下:
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
现在我们需要标记这张表的那些列是主键，那些列是外键，要查询表的主、外键信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系统视图
运行结果如图：
修改我们先前的SQL语句：
复制代码 代码如下:
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_FOREIGN_KEY
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
现在我们已经知道那些列是主键那些是外键，接下来的就是外键列所关联的外检表信息，这里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系统视图，运行该视图如下：
所以修改我们的SQL如下：
复制代码 代码如下:
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'YES\'
ELSE \'NO\'
END AS IS_FOREIGN_KEY,
fkcu.COLUMN_NAME AS FOREIGN_KEY,
fkcu.TABLE_NAME AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
我们的查询结果中显示了太多的NULL，看着不怎么舒服，还有我们的表名应该显示一次就可以，修改SQL如下：
复制代码 代码如下:
SELECT CASE WHEN c.ORDINAL_POSITION = 1
THEN c.TABLE_SCHEMA + \'.\' + c.TABLE_NAME
ELSE \'\'
END AS TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX(\'char\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + \'(\'
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + \')\'
WHEN ( ( CHARINDEX(\'CHAR\', c.DATA_TYPE) > 0
OR CHARINDEX(\'binary\', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + \'(max)\'
WHEN ( CHARINDEX(\'numeric\', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + \'(\' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ \',\' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + \')\'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
ISNULL(c.COLUMN_DEFAULT, \'\') AS COLUMN_DEFAULT ,
CASE WHEN c.IS_NULLABLE = \'YES\' THEN \'√\'
ELSE \'\'
END IS_NULLABLE ,
CASE WHEN tc.CONSTRAINT_TYPE = \'PRIMARY KEY\' THEN \'√\'
ELSE \'\'
END AS IS_PRIMARY_KEY ,
CASE WHEN tc.CONSTRAINT_TYPE = \'FOREIGN KEY\' THEN \'√\'
ELSE \'\'
END AS IS_FOREIGN_KEY ,
ISNULL(fkcu.COLUMN_NAME, \'\') AS FOREIGN_KEY ,
ISNULL(fkcu.TABLE_NAME, \'\') AS FOREIGN_TABLE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND kcu.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE c.TABLE_NAME = \'Address\'
ORDER BY c.ORDINAL_POSITION
运行结果如图：
有不对的地方还请大家拍砖！
本文地址：https://www.stayed.cn/item/22323
转载请注明出处。
本站部分内容来源于网络,如侵犯到您的权益,请 联系我
我的博客
人生若只如初见，何事秋风悲画扇。
我的标签
随笔档案
- 2024-02(2)
- 2023-06(1)
- 2023-05(1)
- 2023-04(14)
- 2023-03(3)
- 2023-01(6)
- 2022-12(5)
- 2022-11(5)
- 2022-07(2)
- 2022-06(4)
- 2022-05(3)
- 2022-03(1)
- 2021-12(6)
- 2021-11(1)
- 2021-10(3)
- 2021-09(5)
- 2021-07(5)
- 2021-02(2)
- 2021-01(7)
- 2020-12(18)
- 2020-11(14)
- 2020-10(12)
- 2020-09(10)
- 2020-08(22)
- 2020-07(2)
- 2020-06(1)
- 2020-04(5)
- 2020-03(9)
- 2020-02(7)
- 2020-01(9)
- 2019-12(8)
- 2019-11(10)
- 2019-10(11)
- 2019-09(17)
- 2019-08(16)
- 2019-07(6)
- 2019-06(3)
- 2019-04(1)
- 2019-03(8)
- 2019-02(5)
- 2019-01(1)
- 2018-11(2)
- 2018-10(3)
- 2018-09(1)
- 2018-08(3)
- 2018-07(3)
- 2018-06(7)
- 2018-04(4)
- 2018-03(5)
- 2018-02(4)
- 2018-01(22)
- 2017-12(3)
- 2017-11(5)
- 2017-10(15)
- 2017-09(26)
- 2017-08(1)
- 2017-07(3)