字段的描述存在系统表SYSPROPERTIES中。到导数据的时候。不过导入导出的时候,系统表是不能选择的。所以,没有。 我这有一段代码,你可以参考这个。得到描述 select b.[name] as 表名,c.[name] as 字段名,t.[name] as 字段类型,c.length as 字段长度,c.xscale as 小数位数,b.[id] as tid,c.colid as colid into #temp from syscolumns c,systypes t,sysobjects b where b.xtype='u' and t.xtype=c.xtype and c.[id]=b.[id]select 表名,字段名,字段类型,字段长度,小数位数,p.value as 字段描述 from #temp,sysproperties p where #temp.[tid]=p.[id] and #temp.colid=p.smallid
SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序号', a.name N'字段名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识', (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) N'主键', b.name N'类型', a.length N'占用字节数', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', (case when a.isnullable=1 then '√'else '' end) N'允许空', isnull(e.text,'') N'默认值', isnull(g.[value],'') AS N'字段说明' --into ##txFROM 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 order by object_name(a.id),a.colorder
SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序号', a.name N'字段名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识', (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) N'主键', b.name N'类型', a.length N'占用字节数', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', (case when a.isnullable=1 then '√'else '' end) N'允许空', isnull(e.text,'') N'默认值', isnull(g.[value],'') AS N'字段说明' --into ##txFROM 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 order by object_name(a.id),a.colorder
select left(A.name,20) col, left(C.name,20) type, A.length from syscolumns A, sysobjects B, systypes C where A.id = B.id and A.xtype = C.xtype and B.name = 'a1' order by A.colorder
--how to get discription of fields: SELECT * FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'tablename', 'columnname', default)
1 如何在程序中取出字段的描述? select object_name(c.id) as 表名 ,c.name as 字段名 ,t.name 数据类型 ,c.prec as 长度 ,p.value as 字段说明 from syscolumns c inner join systypes t on c.xusertype=t.xusertype left join sysproperties p on c.id=p.id and c.colid = p.smallid where objectproperty(c.id,'IsUserTable')=1 2 为什么在导出到其它SQL Server数据库中,描述就会丢失? 可以的: 导出方式:你要选择"在sql server数据库之间复制对象和数据" 并在"选择要复制的对象"这一步,"创建目的对象",要选择"包含扩展属性"默认是不复制扩展属性的,而字段描述是扩展属性,所以不选就会丢失
我这有一段代码,你可以参考这个。得到描述
select b.[name] as 表名,c.[name] as 字段名,t.[name] as 字段类型,c.length as 字段长度,c.xscale as 小数位数,b.[id] as tid,c.colid as colid into #temp from syscolumns c,systypes t,sysobjects b
where b.xtype='u' and t.xtype=c.xtype and c.[id]=b.[id]select 表名,字段名,字段类型,字段长度,小数位数,p.value as 字段描述 from #temp,sysproperties p where #temp.[tid]=p.[id] and #temp.colid=p.smallid
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##txFROM 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
order by object_name(a.id),a.colorder
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##txFROM 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
order by object_name(a.id),a.colorder
where A.id = B.id
and A.xtype = C.xtype
and B.name = 'a1'
order by A.colorder
SELECT *
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'tablename', 'columnname', default)
select object_name(c.id) as 表名
,c.name as 字段名
,t.name 数据类型
,c.prec as 长度
,p.value as 字段说明
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
left join
sysproperties p
on c.id=p.id and c.colid = p.smallid
where objectproperty(c.id,'IsUserTable')=1
2 为什么在导出到其它SQL Server数据库中,描述就会丢失?
可以的:
导出方式:你要选择"在sql server数据库之间复制对象和数据"
并在"选择要复制的对象"这一步,"创建目的对象",要选择"包含扩展属性"默认是不复制扩展属性的,而字段描述是扩展属性,所以不选就会丢失