我想要取得如Master数据库中dtproperties这个表中所以字段名,和对应字段的数据类型的长底???????
解决方案 »
- 数据库多表关联查询 40 - 60 万条数据(怎么提升效率) SQL Server 2008
- 在SQLServer中注冊DLL文件麼?
- 插入数据时的 换码字符 问题,请大家帮忙!
- 自定义函数最后面不加GO可以么?不加有什么不好?我试了,好像不加也能达到效果呀?怎么回事?
- sql server 2008复制 同步啊!!!!! (100分!)
- 大侠,这样的SQL语句怎么写
- 一开机就有个mssearch.exe 进程在运行,占用了80%左右的cpu,这是个什么进程,为什么杀不掉,我的cpu 是P4 (1.8G)
- 小菜鸟的问题
- 询问一条语句
- ****查询相同字段的记录*****
- 没有公网IP,只有ADSL动态分配的IP,怎样实现远程连接SQL数据库
- sql一对多语句一条 同志们帮忙解决
当前库的所有表信息
select * from sysobjects where xtype = 'u' 当前表的所有信息(包括字段名 大小等)
select * from syscolumns where id=object_id('表名')
a.colorder 字段序号,
cast(a.name as varchar) 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then convert(char(4),IDENT_SEED(d.name)) else '' end)标识SEED,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then convert(char(4),IDENT_INCR(d.name)) else '' end)标识INCR,
(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 '√' else '' end) 主键,
cast(b.name as varchar) 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
cast(isnull(e.text,'') as varchar) 默认值,
isnull(g.[value],'') AS 字段说明 FROM syscolumns a left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' --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
where d.name = 'dtproperties'
order by a.id,a.colorder/*表名 字段序号 字段名 标识 标识SEED 标识INCR 主键 类型 占用字节数 长度 小数位数 允许空 默认值 字段说明
------------------------------ ------ ------------------------------ ---- ------ ------ ---- ------------------------------ ------ ----------- ----------- ---- ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
dtproperties 1 id √ 1 1 √ int 4 10 0
2 objectid int 4 10 0 √
3 property √ varchar 64 64 0
4 value varchar 255 255 0 √
5 lvalue image 16 2147483647 0 √
6 version int 4 10 0 (0)
7 uvalue nvarchar 510 255 0 √ (所影响的行数为 7 行)*/
go SELECT 表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when 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))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
索引=(SELECT top 1 name FROM sysindexes m , sysindexkeys n WHERE m.id=n.id and m.indid=n.indid and n.colid=a.colid and m.id=a.id),
字段说明=isnull(g.[value],''),
创建时间=d.crdate
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' 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
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='dtproperties'
Order by a.id,a.colorder
EXEC sp_columns 'youTableName'