SQLSERVER查询约束 
 1 SELECT   TAB.NAME AS [表名], '主键' AS [是否唯一索引], IDXCOL.IS_DESCENDING_KEY AS [是否降序], IDX.NAME AS [约束名称], 
 2                 IDX.TYPE_DESC AS [约束类型], COL.NAME AS [约束列名], INDEX_COLUMN_ID
 3 FROM      SYS.INDEXES IDX JOIN
 4                 SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND 
 5                 IDX.IS_PRIMARY_KEY = 1) JOIN
 6                 SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
 7                 SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)
 8 UNION ALL
 9 SELECT   TAB.NAME AS [表名], 'UQ唯一索引' AS [是否唯一索引], IDXCOL.IS_DESCENDING_KEY AS [是否降序], 
10                 IDX.NAME AS [约束名称], IDX.TYPE_DESC AS [约束类型], COL.NAME AS [约束列名], INDEX_COLUMN_ID
11 FROM      SYS.INDEXES IDX JOIN
12                 SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND 
13                 IDX.IS_UNIQUE_CONSTRAINT = 1) JOIN
14                 SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
15                 SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)
16 UNION ALL
17 SELECT   TAB.NAME AS [表名], CASE IDX.IS_UNIQUE WHEN 0 THEN '非唯一索引' ELSE '唯一索引' END AS [是否唯一索引], 
18                 IDXCOL.IS_DESCENDING_KEY AS [是否降序], IDX.NAME AS [约束名称], IDX.TYPE_DESC AS [约束类型], 
19                 COL.NAME AS [约束列名], INDEX_COLUMN_ID
20 FROM      SYS.INDEXES IDX JOIN
21                 SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID AND IDX.INDEX_ID = IDXCOL.INDEX_ID AND 
22                 IDX.IS_UNIQUE_CONSTRAINT = 0 AND IS_PRIMARY_KEY = 0) JOIN
23                 SYS.TABLES TAB ON (IDX.OBJECT_ID = TAB.OBJECT_ID) JOIN
24                 SYS.COLUMNS COL ON (IDX.OBJECT_ID = COL.OBJECT_ID AND IDXCOL.COLUMN_ID = COL.COLUMN_ID)
25 
26

 

 posted on 2019-12-31 11:20  代码改变世界&1024  阅读(3370)  评论(0)    收藏  举报