探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
更新時間：2013年06月21日 11:15:02 作者：
本篇文章是對SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息進行了詳細的分析介紹，需要的朋友參考下
接著上篇文章《解析SQL 表結構信息查詢 含主外鍵、自增長》里面提到了INFORMATION_SCHEMA視圖，其實到了SQL 2005微軟都主推大家使用INFORMATION_SCHEMA系統(tǒng)視圖，而不是在使用sys東東了，當然目前還是有許多信息只能通過sys視圖來查詢。這里我們還是以查詢表結果信息為例來說明一些主要的INFORMATION_SCHEMA視圖的使用。
首先我們需要查詢列的信息，這需要用到[INFORMATION_SCHEMA].[COLUMNS]系統(tǒng)視圖來查詢數據列的信息，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] 系統(tǒng)視圖
運行結果如圖：
修改我們先前的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]系統(tǒng)視圖，運行該視圖如下：
所以修改我們的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]系統(tǒng)視圖來查詢數據列的信息，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] 系統(tǒng)視圖
運行結果如圖：
修改我們先前的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]系統(tǒng)視圖，運行該視圖如下：
所以修改我們的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
運行結果如圖：
有不對的地方還請大家拍磚！
相關文章
mysql 5.6.14 win32 解壓縮版（免安裝）安裝配置教程
這篇文章主要介紹了mysql 5.6.14 win32 解壓縮版（免安裝）安裝配置教程，感興趣的小伙伴們可以參考一下2016-11-11
在開發(fā)過程中我們不管是用來測試性能還是在生產環(huán)境中頁面展示好看一點,?經常需要一些測試數據,?本文主要介紹了兩種常用的MySQL測試數據批量生成方式,希望對大家有所幫助2023-11-11
本文主要介紹的是用mysql_real_escape_string對用戶提交的表單數據進行轉義處理和通過addslashes以及mysql_escape_string這3個類似功能的函數用法區(qū)別2013-04-04
mysql分頁時offset過大的Sql優(yōu)化經驗分享
mysql分頁是我們在開發(fā)經常遇到的一個功能，最近在實現該功能的時候遇到一個問題，所以這篇文章主要給大家介紹了關于mysql分頁時offset過大的Sql優(yōu)化經驗，文中介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面跟著小編來一起看看吧。2017-08-08
最近在工作中，涉及到一個數據遷移功能，從一個txt文本文件導入到MySQL功能。數據遷移，在互聯網企業(yè)可以說經常碰到，而且涉及到千萬級、億級的數據量是很常見的。今天我們就來談談MySQL怎么高性能插入千萬級的數據。2021-05-05
MySQL創(chuàng)建和刪除數據庫的命令及相關PHP腳本的操作方法
這篇文章主要介紹了MySQL創(chuàng)建和刪除數據庫的命令及相關PHP腳本的操作方法,這里主要講述Linux中在mysqladmin下的命令操作,需要的朋友可以參考下2015-11-11
這篇文章主要給大家介紹了如何利用MYSQL實現每隔10分鐘進行分組統(tǒng)計的方法，文中給出了詳細的示例代碼，相信對大家的理解和學習具有一定的參考借鑒價值，有需要的朋友們下面來一起看看吧。2016-12-12
本文主要介紹了Mysql查詢條件判斷是否包含字符串的方法實現,主要包括like,locate,postion,instr,find_in_set這幾種方法,具有一定的參考價值,感興趣的可以了解一下2023-10-10
MySQL/Postgrsql 詳細講解如何用ODBC接口訪問MySQL指南
2008-01-01
這篇文章主要介紹了MySQL中從庫延遲狀況排查的一則案例,針對其從庫無業(yè)務狀態(tài)下的CPU大量占用情況,需要的朋友可以參考下2015-05-05