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