如何获取一个表的,字段,类型,长度,是否主键,是否为空,注释 等信息
//可以获取表名称select name from sysobjects where type='U'
//可以获取列的相关信息
select * from syscolumns where id=object_id('CODEFIELD')
问题是, syscolumns 里面大部分的字段我都不知道干什么的能给个解释吗?
例如type=38 39 。。。对应的都是什么类型。 select type,name from master..systypes select object_name(c.id) objname, c.colid colid, c.name colname, (case (c.status & 128) when 0 then t2.name else 'identity' end) coltype,
c.length collen, (case (c.status & 8) when 0 then 'not null' else 'null' end) allownull
from syscolumns c, systypes t1, systypes t2
where c.id = object_id('CODEFIELD')
and c.usertype = t1.usertype and t1.type = t2.type
and t2.usertype = (select min(m.usertype) from dbo.systypes m where t2.type = m.type)
order by 1, 2
获取表名 列名 字段类型,长度(精度)主键 默认值等信息
本帖最后由 he251377753 于 2011-03-31 09:48 编辑select O.name tablename,
C.name column_1,
isnull(X.xtname, isnull(get_xtypename(C.xtype,C.xdbid), T.name)) typename_1 ,
caseisnull(X.xtname, isnull(get_xtypename(C.xtype,C.xdbid), T.name)) when 'decimal' then convert (varchar(4) ,C.prec)elseconvert (varchar(4) ,C.length ) endlength_1 ,
caseC.statuswhen 8 then 'Y' ELSE 'N' end isnull_1,
isnull((selectisnull(str_replace(str_replace( text, "DEFAULT '",'' ) , "'",'') ,'')FROM syscomments D,sysprocedures P
WHERE D.id = C.cdefault AND P.id = D.id
AND P.sequence = 0 AND P.status & 4096 = 4096),'') default_1 ,
isnull( (select'PK'from (SELECTobject_name(id) tabname,index_col( object_name(id) ,indid,1) columnnameFROM sysindexes WHERE status & 2048=2048
unionALL
SELECTobject_name(id),index_col( object_name(id) ,indid,2)FROM sysindexes WHERE status & 2048=2048
union ALL
SELECTobject_name(id), index_col( object_name(id) ,indid,3)FROM sysindexes WHERE status & 2048=2048
union ALL
SELECTobject_name(id), index_col( object_name(id) ,indid,4)FROM sysindexes WHERE status & 2048=2048
union ALL
select object_name(id), index_col( object_name(id) ,indid,5) FROM sysindexes WHERE status & 2048=2048
unionALL
SELECTobject_name(id), index_col( object_name(id) ,indid,6) FROM sysindexes WHERE status & 2048=2048 ) pk
wherecolumnname is not null
andtabname =O.name
andcolumnname = C.name ) ,'') PK_1,
0null_1,
0no_1,
0count_1
FROM syscolumns C, systypes T, sysxtypes X, sysobjects O
WHERE C.usertype *= T.usertype
AND C.xtype *= X.xtid
AND C.id = O.id
AND O.type = 'U' 这个确实有用!
对一个新的应用系统不熟,这个帮助可不小! 参考sp_help存储过程 或者:
sp_columns 这个存储过程。
也可参考:Sybase ASE中实现类似oracle的sqlplus中desc命令来简要显示表结构的存储过程
页:
[1]