获取数据库中的所有用户表、取数据表的描述信息包括描述说明

--获取所有数据库名

Select Name FROM Master..SysDatabases ORDER BY Name

www.zzzyk.com

--获取数据库中所有的用户表

Select Name FROM SysObjects Where XType='U' orDER BY Name

--获取数据库中数据表所有字段 (将tbName替换成你的表名即可)

select a.* from syscolumns a, sysobjects b where a.id=b.id and b.name= 'ContentInfo'

www.zzzyk.com

--获取数据表的详细信息 (将tbName替换成你的表名即可)

SELECT CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND parent_obj = a.id AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,

'IsIdentity') = 1 THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name,

'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'

FROM sys.syscolumns AS a INNER JOIN www.zzzyk.com

sys.sysobjects AS b ON a.id = b.id INNER JOIN

sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN

sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN

sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id

WHERE (b.name = 'ContentInfo') AND (c.status <> '1')

--获取自增的字段名称(将 tbName 替换成你的表名即可)

select [name] from syscolumns where

id=object_id(N'tbName') and COLUMNPROPERTY(id,name,'IsIdentity')=1