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.

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

 

 --过滤了系统字段信息

总结

以上是内存溢出为你收集整理的SqlServer字段说明添加，查询，修改！ 查询字段名称和类型。全部内容，希望文章能够帮你解决SqlServer字段说明添加，查询，修改！ 查询字段名称和类型。所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。