查询SqlServer 2005的表结构和描述

 

 

declare   @table_name   as   varchar ( max )
set   @table_name   =   ' your_table_name '     -- 你的表名 
--copyright leechiyang
select  sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable, 
  ( select   count ( * )  from  sys.identity_columns  where  sys.identity_columns. object_id   =  sys.columns. object_id   and  sys.columns.column_id  =  sys.identity_columns.column_id)  as  is_identity ,
  ( select  value  from  sys.extended_properties  where  sys.extended_properties.major_id  =  sys.columns. object_id   and  sys.extended_properties.minor_id  =  sys.columns.column_id)  as  description
   from  sys.columns, sys.tables, sys.types  where  sys.columns. object_id   =  sys.tables. object_id   and  sys.columns.system_type_id = sys.types.system_type_id  and  sys.tables.name = @table_name   order   by  sys.columns.column_id