SQLServer2005,2000获取表结构：字段名、类型、长度、主键、非空、注释

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQLServer2005,2000获取表结构：字段名、类型、长度、主键、非空、注释，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2087字，纯文字阅读大概需要3分钟。

内容图文

原文链接：http://www.cnblogs.com/xiaotiannet/p/3838150.html

SQLServer 2005

SELECT
    d.name N'TableName',
    d.xtype N'TableType',
    a.colorder N'ColumnIndex',
    a.name N'ColumnName',
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IdnetityFG',
    (case when (SELECT count(*)  FROM sysobjects  WHERE 
               (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND 
                        (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND 
                                  (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))
                                  )
                        )
                        )
                ) AND (xtype = 'PK')
                )>0 then '1' else '0' end
    ) N'IsPK',
    b.name N'DataType',
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'Length',
    (case when a.isnullable=1 then '1'else '0' end) N'IsNullable',
    isnull(e.text,'') N'Default',
    g.value N'Description'    
FROM   syscolumns   a 
    left join systypes b on   a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id   and   d.xtype in ('U','V') and   d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
order by object_name(a.id),a.colorder

 

SQLServer2000

SELECT
    d.name N'TableName',
    d.xtype N'TableType',
    a.colorder N'ColumnIndex',
    a.name N'ColumnName',
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IdnetityFG',
    (case when (SELECT count(*)  FROM sysobjects  WHERE 
               (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND 
                        (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND 
                                  (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))
                                  )
                        )
                        )
                ) AND (xtype = 'PK')
                )>0 then '1' else '0' end
    ) N'IsPK',
    b.name N'DataType',
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'Length',
    (case when a.isnullable=1 then '1'else '0' end) N'IsNullable',
    isnull(e.text,'') N'Default',
    g.value N'Description'    
FROM   syscolumns   a 
    left join systypes b on   a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id   and   d.xtype in ('U','V') and   d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    left join sysproperties g on a.id=g.id and a.colid=g.smallid
order by object_name(a.id),a.colorder

--select * from sysproperties

转载于:https://www.cnblogs.com/xiaotiannet/p/3838150.html

内容总结

以上是互联网集市为您收集整理的SQLServer2005,2000获取表结构：字段名、类型、长度、主键、非空、注释全部内容，希望文章能够帮你解决SQLServer2005,2000获取表结构：字段名、类型、长度、主键、非空、注释所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---