- 论坛徽章:
- 0
|
获取表名 列名 字段类型,长度(精度)主键 默认值等信息
本帖最后由 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 ,
case isnull(X.xtname, isnull(get_xtypename(C.xtype,C.xdbid), T.name)) when 'decimal' then convert (varchar(4) ,C.prec)else convert (varchar(4) ,C.length ) end length_1 ,
case C.status when 8 then 'Y' ELSE 'N' end isnull_1 ,
isnull((select isnull(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 ( SELECT object_name(id) tabname, index_col( object_name(id) ,indid,1) columnname FROM sysindexes WHERE status & 2048=2048
union ALL
SELECT object_name(id), index_col( object_name(id) ,indid,2) FROM sysindexes WHERE status & 2048=2048
union ALL
SELECT object_name(id), index_col( object_name(id) ,indid,3) FROM sysindexes WHERE status & 2048=2048
union ALL
SELECT object_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
union ALL
SELECT object_name(id), index_col( object_name(id) ,indid,6) FROM sysindexes WHERE status & 2048=2048 ) pk
where columnname is not null
and tabname = O.name
and columnname = C.name ) ,'') PK_1,
0 null_1,
0 no_1,
0 count_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' |
|