Sqlserver数据字典

以下内容出自cookbook

/*列出模式中的表*/
select table_name
  from information_schema.tables
 where table_schema = 'SMEAGOL'


/*列出表的列*/
select column_name, data_type, ordinal_position
  from information_schema.columns
 where table_schema = 'SMEAGOL'
   and table_name   = 'EMP'


/*列出表的索引列*/
select a.name table_name,
       b.name index_name,
       d.name column_name,
       c.index_column_id
  from sys.tables a,
       sys.indexes b,
       sys.index_columns c,
       sys.columns d
where a.object_id = b.object_id
  and b.object_id = c.object_id
  and b.index_id  = c.index_id
  and c.object_id = d.object_id
  and c.column_id = d.column_id
  and a.name      = 'EMP'


/*列出表约束*/
select a.table_name,
       a.constraint_name,
       b.column_name,
       a.constraint_type
  from information_schema.table_constraints a,
       information_schema.key_column_usage b
where a.table_name      = 'EMP'
  and a.table_schema    = 'SMEAGOL'
  and a.table_name      = b.table_name
  and a.table_schema    = b.table_schema
  and a.constraint_name = b.constraint_name


/*列出没有相应索引的外键*/
select fkeys.table_name,
       fkeys.constraint_name,
       fkeys.column_name,
       ind_cols.index_name
  from (
select a.object_id,
       d.column_id,
       a.name table_name,
       b.name constraint_name,
       d.name column_name
  from sys.tables a
       join
       sys.foreign_keys b
    on ( a.name          = 'EMP'
         and a.object_id = b.parent_object_id
       )
       join
       sys.foreign_key_columns c
   on (  b.object_id = c.constraint_object_id )
      join
      sys.columns d
   on (    c.constraint_column_id = d.column_id
       and a.object_id            = d.object_id
      )
      ) fkeys
      left join
      (
elect a.name index_name,
      b.object_id,
      b.column_id
 from sys.indexes a,
      sys.index_columns b
where a.index_id = b.index_id
      ) ind_cols
   on (     fkeys.object_id = ind_cols.object_id
        and fkeys.column_id = ind_cols.column_id )
where ind_cols.index_name is null