sql server中数据约束相关的查询

 根据表名查找数据约束

SELECT *
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  TABLE_NAME = 'CMS_EventLog';

 

SELECT TABLE_NAME ,
       COLUMN_NAME ,
       CONSTRAINT_NAME -- <<-- the one you want! 
FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE  TABLE_NAME = 'tbm_cti_CustomTableItem_Company';

 

CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED
d_lisa_CMS_dev01_v4300 dbo FK_CMS_Event_NodeID_CMS_Tree d_lisa_CMS_dev01_v4300 dbo CMS_EventLog FOREIGN KEY NO NO
d_lisa_CMS_dev01_v4300 dbo FK_CMS_Event_SiteID_CMS_Site d_lisa_CMS_dev01_v4300 dbo CMS_EventLog FOREIGN KEY NO NO
d_lisa_CMS_dev01_v4300 dbo FK_CMS_Event_UserID_CMS_User d_lisa_CMS_dev01_v4300 dbo CMS_EventLog FOREIGN KEY NO NO
d_lisa_CMS_dev01_v4300 dbo PK_CMS_EventLog d_lisa_CMS_dev01_v4300 dbo CMS_EventLog PRIMARY KEY NO NO

 

根据数据约束查找表名

SELECT TABLE_NAME ,
       COLUMN_NAME ,
       CONSTRAINT_NAME -- <<-- the one you want! 
FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE  CONSTRAINT_NAME LIKE '%6878%'

 

作者:Chuck Lu    GitHub