沒有問題啊。
Create Table TEST(ID Int Identity(1, 1), Name Varchar(10))
GO
EXECUTE sp_Addextendedproperty 'MS_Description','哈哈','USER','dbo','table','TEST','column','ID'Select
表名=Case When A.ColOrder=1 Then D.Name Else '' End,
字段名=A.Name,
字段说明=IsNull(G.[value],'')
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 sysproperties G On A.ID=G.ID And A.colid=G.smallid
Where D.Name='TEST'
Order By A.ID,A.ColOrderEXECUTE sp_updateextendedproperty 'MS_Description','哇哈哈哈','USER','dbo','table','TEST','column','ID'Select
表名=Case When A.ColOrder=1 Then D.Name Else '' End,
字段名=A.Name,
字段说明=IsNull(G.[value],'')
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 sysproperties G On A.ID=G.ID And A.colid=G.smallid
Where D.Name='TEST'
Order By A.ID,A.ColOrder
GO
Drop Table TEST
--Result
/*
--加入字段说明後結果
表名 字段名 字段说明
TEST ID 哈哈
Name
--修改字段说明後結果
表名 字段名 字段说明
TEST ID 哇哈哈哈
Name
*/
Create Table TEST(ID Int Identity(1, 1), Name Varchar(10))
GO
EXECUTE sp_Addextendedproperty 'MS_Description','哈哈','USER','dbo','table','TEST','column','ID'Select
表名=Case When A.ColOrder=1 Then D.Name Else '' End,
字段名=A.Name,
字段说明=IsNull(G.[value],'')
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 sysproperties G On A.ID=G.ID And A.colid=G.smallid
Where D.Name='TEST'
Order By A.ID,A.ColOrderEXECUTE sp_updateextendedproperty 'MS_Description','哇哈哈哈','USER','dbo','table','TEST','column','ID'Select
表名=Case When A.ColOrder=1 Then D.Name Else '' End,
字段名=A.Name,
字段说明=IsNull(G.[value],'')
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 sysproperties G On A.ID=G.ID And A.colid=G.smallid
Where D.Name='TEST'
Order By A.ID,A.ColOrder
GO
Drop Table TEST
--Result
/*
--加入字段说明後結果
表名 字段名 字段说明
TEST ID 哈哈
Name
--修改字段说明後結果
表名 字段名 字段说明
TEST ID 哇哈哈哈
Name
*/
我是把改描述写成了存储过程,但是我用以下语句在新建的表里试了下都可以在企业管理器中看到描述的改变在我以前的表里却不行
我执行了之后 在查询分析器中点表tb_km_jhd的右键的扩展属性里可以看到描述有做更改
但是在企业管理器右键点击此表的设计表,却看不到描述,真郁闷哦declare @TableName varchar(50)
declare @ColumnName varchar(50)
declare @Re varchar(50)
set @TableName = 'tb_km_jhd'
set @ColumnName = 'sysid'
set @Re = '哇哈哈哈'
IF (SELECT COUNT(*) FROM ::FN_LISTEXTENDEDPROPERTY('MS_DESCRIPTION', 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName)) = 0
EXEC SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION', @Re, 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName ELSE
EXEC SP_UPDATEEXTENDEDPROPERTY 'MS_DESCRIPTION', @Re, 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName;create procedure UpdateRe(@Re varchar(100),@TableName varchar(50),@ColumnName varchar(50))
as
begin
IF (SELECT COUNT(*) FROM ::FN_LISTEXTENDEDPROPERTY('MS_DESCRIPTION', 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName)) = 0
EXEC SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION', @Re, 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName ELSE
EXEC SP_UPDATEEXTENDEDPROPERTY 'MS_DESCRIPTION', @Re, 'USER', 'DBO', 'TABLE', @TableName, 'COLUMN', @ColumnName;
endexec updateRe '哇哈哈哈','tb_km_jhd','sysid'此顽固表的脚本:
CREATE TABLE [tb_km_jhd] (
[sysid] [int] IDENTITY (1, 1) NOT NULL ,
[yw] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[kmbh] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[kmmc] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[jhd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Re] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO