SQL SERVER之查询 未创建索引的外键 和 所有外键及索引

查询未创建索引的外键

  SELECT  *
  FROM    sys.foreign_keys fk
  WHERE   EXISTS
          (
          SELECT  *
          FROM    sys.foreign_key_columns fkc
          WHERE   fkc.constraint_object_id = fk.object_id
                  AND NOT EXISTS
                  (
                  SELECT  *
                  FROM    sys.index_columns ic
                  WHERE   ic.object_id = fkc.parent_object_id
                          AND ic.column_id = fkc.parent_column_id
                          AND ic.index_column_id = fkc.constraint_column_id
                  )
          )

查询表或数据库中的所有外键

  select 
  A.name as 约束名,
  object_name(b.parent_object_id) as 外健表,
  c.name as 外键列,
  object_name(b.referenced_object_id) as 主健表,
  D.name as 主键列
  from sys.foreign_keys A
  inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
  inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id 
  inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id 
  where object_name(B.referenced_object_id)='TB_TestConfigTemplate';  --主键表

查询一个表或数据库中的索引及索引列

  SELECT indexname = a.name , tablename = c. name , indexcolumns = d .name , a .indid
  FROM sysindexes a JOIN sysindexkeys b ON a .id = b .id  AND a .indid = b.indid
       JOIN sysobjects c ON b .id = c .id
       JOIN syscolumns d ON b .id = d .id  AND b .colid = d .colid
  WHERE a .indid NOT IN ( 0 , 255 )  --indid=1代表聚集索引 indid>1代表非聚集索引
  -- and c.xtype='U' and c.status>0 -- 查所有用户表
  --AND c.name = 'DatabaseLog' --查指定表
  ORDER BY c.name,a.name,d.name

原文链接：https://www.cnblogs.com/lfxiao/p/6760507.html