SqlServer 2005 快速设置字段/表 的描述字段
在做数据库设计的时候,字段/表的 描述/备注 字段设置非常重要,在使用orm工具生成代码的时候也要读取 描述/备注 的内容,当然在设计器的时候添加这些信息还是比较方便的,但是使用sql脚本的时候,看起来就很复杂了。
我先想能不能直接在create table的时候直接添加描述字段,不过找遍了sql的语法也不支持这种写法,使用sp_addextendedproperty 我又感觉太麻烦,很多没必要的参数设置,于是自己写了一个存储过程来进行快速的设置:
Code
/****** 对象: StoredProcedure [dbo].[SetDescription] 脚本日期: 05/22/2009 13:46:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.SetDescription
(
@Description sql_variant,
@TableName sysname,
@ColumnName sysname=NULL
)
AS
BEGIN
IF (@ColumnName IS NULL)
BEGIN
IF EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description', N'USER',N'dbo', N'TABLE',@TableName, NULL, NULL))
EXEC sp_dropextendedproperty @name=N'MS_Description', @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName
EXEC sp_addextendedproperty @name=N'MS_Description', @value=@Description, @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description', N'USER',N'dbo', N'TABLE',@TableName, N'COLUMN',@ColumnName))
EXEC sp_dropextendedproperty @name=N'MS_Description', @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ColumnName
EXEC sp_addextendedproperty @name=N'MS_Description', @value=@Description, @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ColumnName
END
END
GO
/****** 对象: StoredProcedure [dbo].[SetDescription] 脚本日期: 05/22/2009 13:46:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.SetDescription
(
@Description sql_variant,
@TableName sysname,
@ColumnName sysname=NULL
)
AS
BEGIN
IF (@ColumnName IS NULL)
BEGIN
IF EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description', N'USER',N'dbo', N'TABLE',@TableName, NULL, NULL))
EXEC sp_dropextendedproperty @name=N'MS_Description', @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName
EXEC sp_addextendedproperty @name=N'MS_Description', @value=@Description, @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description', N'USER',N'dbo', N'TABLE',@TableName, N'COLUMN',@ColumnName))
EXEC sp_dropextendedproperty @name=N'MS_Description', @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ColumnName
EXEC sp_addextendedproperty @name=N'MS_Description', @value=@Description, @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@TableName, @level2type=N'COLUMN',@level2name=@ColumnName
END
END
GO
使用如下:
EXEC dbo.SetDescription N'功能描述',N'sys_Functions',N'FRemark' --更改表 sys_Functions 的字段 FRemark 的描述为:功能描述
EXEC dbo.SetDescription N'功能表',N'sys_Functions' --更改表 sys_Functions 的描述为:功能表
GO
EXEC dbo.SetDescription N'功能表',N'sys_Functions' --更改表 sys_Functions 的描述为:功能表
GO