SqlServer字段说明添加，查询，修改！ 查询字段名称和类型。

1.查询两个表的字段说明

? SELECT t.[name] AS [表名],c.[name] AS [字段名],cast(ep.[value]?

? as varchar(100)) AS [字段说明]

? FROM sys.tables AS t INNER JOIN sys.columns?

? AS c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS ep?

? ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1?

? and t.[name]='table1' or t.[name]='table2'

? and c.[name] in ('table2字段','table2字段')

? or c.[name] in ('table1字段,'table1字段')

?

2.添加字段的名称

? EXEC

? sys.sp_addextendedproperty @name=N'MS_Description',

? @value=N'字段说明' , @level0type=N'SCHEMA',@level0name=N'dbo',

? @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',

? @level2name=N'字段名'

? GO

?

3.修改字段的名称

?

? BEGIN TRANSACTION

? GO

? DECLARE @v sql_variant

? SET @v = N'说明信息'

? EXECUTE sys.sp_updateextendedproperty N'MS_Description',

? @v, N'SCHEMA',N'dbo',N'TABLE',N'表名, N'COLUMN', N'字段名'

? GO

? COMMIT

?

?

?

4.查询数据库字段信息，和类型

select a.name as zdname,a.length,b.name as zdtype from syscolumns a,systypes b,sysobjects c

where a.xtype=b.xtype and a.id=c.id and c.name= 'table' --没有过滤系统字段信息

?

select a.name,b.name from syscolumns a,sysobjects c?

where a.xtype=b.xtype and a.id=c.id and c.name= 'table'?

AND B.NAME!='SYSNAME'?????????? --过滤了系统字段信息

?

?

select a.name,sysobjects c

where a.xtype=b.xtype and a.id=c.id and c.name= 'table' and charindex('sysname',b.name) = 0

?

?--过滤了系统字段信息

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!