mysql外键表视图_探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息

接着上篇文章《

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

运行结果如图：

有不对的地方还请大家拍砖！