超级 有用 的sql 语句 （用于sql server 服务器） 超级 有用 的sql 语句 ，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键， 语句 如下： ( 分析 sql server 数据库 表 结构 专用 ) 以下是sqlserver2000 select sysobjects.name as 表
超级有用的sql语句 （用于sql server 服务器）
超级有用的sql语句 ，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键，语句如下：
(分析sql server 数据库表结构专用)
以下是sqlserver2000
select sysobjects.name as 表名,
       syscolumns.name as 列名,
       systypes.name as 列类型,
       syscolumns.length as 列长度,
       isnull(sysproperties.value, syscolumns.name) as 列说明,
       case
         when syscolumns.name in
              (select 主键 = a.name
                 from syscolumns a
                inner join sysobjects b on a.id = b.id
                                       and b.xtype = 'u'
                                       and b.name 'dtproperties'
                where exists (select 1
                         from sysobjects
                        where xtype = 'pk'
                          and name in
                              (select name
                                 from sysindexes
                                where indid in
                                      (select indid
                                         from sysindexkeys
                                        where id = a.id
                                          and colid = a.colid)))
                  and b.name = sysobjects.name) then
          1
         else
          0
       end as 是否主键
  from sysobjects, systypes, syscolumns
  left join sysproperties on (syscolumns.id = sysproperties.id and
                             syscolumns.colid = sysproperties.smallid)
 where (sysobjects.xtype = 'u' or sysobjects.xtype = 'v')
   and sysobjects.id = syscolumns.id
   and systypes.xtype = syscolumns.xtype
   and systypes.name 'sysname'
   and sysobjects.name like '%'
 order by sysobjects.name, syscolumns.colid
以下是sqlserver 2005版本的语句
select sysobjects.name as 表名,
       syscolumns.name as 列名,
       systypes.name as 列类型,
       syscolumns.length as 列长度,
       isnull(sys.extended_properties.value, syscolumns.name) as 列说明,
       case
         when syscolumns.name in
              (select 主键 = a.name
                 from syscolumns a
                inner join sysobjects b on a.id = b.id
                                       and b.xtype = 'u'
                                       and b.name 'dtproperties'
                where exists (select 1
                         from sysobjects
                        where xtype = 'pk'
                          and name in
                              (select name
                                 from sysindexes
                                where indid in
                                      (select indid
                                         from sysindexkeys
                                        where id = a.id
                                          and colid = a.colid)))
                  and b.name = sysobjects.name) then
          1
         else
          0
       end as 是否主键
  from sysobjects, systypes, syscolumns
  left join sys.extended_properties on (syscolumns.id = sys.extended_properties.major_id and
                             syscolumns.colid = sys.extended_properties.minor_id)
 where (sysobjects.xtype = 'u' or sysobjects.xtype = 'v')
   and sysobjects.id = syscolumns.id
   and systypes.xtype = syscolumns.xtype
   and systypes.name 'sysname'
   and sysobjects.name like 'xjy%'
 order by sysobjects.name, syscolumns.colid