关闭

如何用表来管理SQLServer中的扩展属性

76人阅读 评论(0) 收藏 举报
分类:

1执行以下脚本创建数据表和列的扩展属性表dc_util_table_desc和dc_util_column_desc

2. EXEC Proc_Util_Desc_GetColumnNameToDescTable , 生成现有表的描述对应记录

3. EXEC Proc_Util_Desc_GetTableNameToDescTable, 生成现有列的描述对应记录

4. 查看数据表和列的扩展属性表dc_util_table_desc和dc_util_column_desc

5. 对dc_util_table_desc和dc_util_column_desc的描述列进行更新

6. 查看数据库表和列的扩展属性是否也有被更新

--1.1 建表(存放表的描述):dbo.dc_util_table_desc
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_table_desc]') AND type in (N'U'))
    DROP TABLE [dbo].[dc_util_table_desc]
GO
CREATE TABLE [dbo].[dc_util_table_desc](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tableName] [varchar](100) NULL,
    [tableDesc] [nvarchar](200) NULL,
 CONSTRAINT [PK_dc_util_table_desc] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
--1.2 建表(存放列的描述):[dc_util_column_desc]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_column_desc]') AND type in (N'U'))
    DROP TABLE [dbo].[dc_util_column_desc]
GO
CREATE TABLE [dbo].[dc_util_column_desc](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tableName] [varchar](100) NULL,
    [columnName] [varchar](100) NULL,
    [columnDesc] [nvarchar](200) NULL,
 CONSTRAINT [PK_dc_util_column_desc] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_dc_util_column_desc_tableName_columnName] UNIQUE NONCLUSTERED 
(
    [tableName] ASC,
    [columnName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
--2.1 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_DeleteInvalidData]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData]
GO
-- =============================================
-- Author:      yenange
-- Create date: 2014-05-29
-- Description: 删除 dc_util_table_desc 表和 
--              dc_util_column_desc 表中不正确的数据
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData] 
AS
BEGIN
    SET NOCOUNT ON;
    --删除 dc_util_table_desc 中的无效数据
    DELETE FROM dbo.dc_util_table_desc WHERE NOT EXISTS (
        SELECT 1 FROM sys.tables T WHERE dbo.dc_util_table_desc.tableName=T.name
    ) 
    --删除 dc_util_column_desc 中的无效数据
    DELETE
    FROM   dbo.dc_util_column_desc
    WHERE  NOT EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.columns c ON  t.object_id = c.object_id
                            WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE  NAME = 'dbo')
                                AND dbo.dc_util_column_desc.tableName=t.name AND dbo.dc_util_column_desc.columnName=c.name 
                        )
END
GO
 
--2.2 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_GetTableNameToDescTable]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_Util_Desc_GetTableNameToDescTable]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将以 @tablePrefix 为前缀的表名和表对应的扩展属性 insert 到 dc_util_table_desc 表中去.
--              @tablePrefix 如果为 '' 或者 null, 则为全部表(默认为null)
--              @overrideDesc : 如果已有记录存在,是否覆盖原记录的扩展属性 (默认为1)
-- =============================================
CREATE procedure [dbo].[Proc_Util_Desc_GetTableNameToDescTable] 
    @tablePrefix VARCHAR(100) =null, 
    @overrideDesc BIT =1
AS
BEGIN
    SET NOCOUNT ON
    --删除表中无效的数据
    exec Proc_Util_Desc_DeleteInvalidData
     
    DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))
    --插入以 @tablePrefix 为前缀的表到@t1
    INSERT INTO @t1
    (
        tablename,
        tabledesc
    )
    SELECT convert(VARCHAR(100),t.name),
           convert (nvarchar(200),p.value)
    FROM   sys.tables                         AS t
           LEFT JOIN sys.extended_properties  AS p
                ON  p.major_id = t.object_id
                AND p.minor_id = 0
                AND p.class = 1
                AND p.name = 'MS_Description'
    WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID
                           FROM   sys.schemas
                           WHERE  NAME = 'dbo')
     AND (ISNULL(@tablePrefix,'')='' or t.name LIKE +@tablePrefix+'%' )
     
    DECLARE @i INT
    DECLARE @i_max INT
    DECLARE @t_name VARCHAR(100)
    DECLARE @t_desc NVARCHAR(200)
    SET @i=1
    SELECT @i_max=COUNT(1) FROM @t1
    WHILE @i<=@i_max
        BEGIN
            SELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@i
            IF @overrideDesc=1
                begin
                    IF EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tableName=@t_name)
                        UPDATE dc_util_table_desc SET tableDesc = @t_desc WHERE tableName=@t_name
                    ELSE
                        INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc)
                END
            ELSE
                BEGIN
                    IF NOT EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tableName=@t_name)
                        INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc)
                END
            set @i=@i+1
        END
END
GO
--2.3 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_GetColumnNameToDescTable]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_Util_Desc_GetColumnNameToDescTable]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将以 @tablePrefix 为前缀的表名对应的列和列对应的扩展属性 insert 到 dc_util_column_desc 表中去.
--              @tablePrefix 如果为 '' 或者 null, 则为全部表(默认为null)
--              @overrideDesc : 如果已有记录存在,是否覆盖原记录的扩展属性 (默认为1)
-- =============================================
CREATE procedure [dbo].[Proc_Util_Desc_GetColumnNameToDescTable] 
@tablePrefix VARCHAR(100) =null, 
@overrideDesc BIT =1
AS
BEGIN
    SET NOCOUNT ON
    --删除表中无效的数据
    exec Proc_Util_Desc_DeleteInvalidData
     
    DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),COLUMNNAME VARCHAR(100),columndesc NVARCHAR(200))
    --插入以 @tablePrefix 为前缀的表到@t1
    INSERT INTO @t1
    (
        tablename,
        COLUMNNAME,
        columndesc
    )
    SELECT convert(varchar(100),t.name)          ,
           convert(varchar(100),c.name)              ,
           convert(nvarchar(200),p.value) 
    FROM   sys.tables                         AS t
           LEFT JOIN sys.columns c
                ON  t.object_id = c.object_id
           LEFT JOIN sys.extended_properties  AS p
                ON  p.major_id = t.object_id
                AND p.minor_id = c.column_id
                AND p.class = 1
                AND p.name = 'MS_Description'
    WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID
                           FROM   sys.schemas
                           WHERE  NAME = 'dbo')
     AND (ISNULL(@tablePrefix,'')='' or t.name LIKE +@tablePrefix+'%')
     
     
    DECLARE @i INT
    DECLARE @i_max INT
    DECLARE @t_name VARCHAR(100)
    DECLARE @col_name VARCHAR(100)
    DECLARE @col_desc NVARCHAR(200)
    SET @i=1
    SELECT @i_max=COUNT(1) FROM @t1
    WHILE @i<=@i_max
        BEGIN
            SELECT @t_name=tablename,@col_name=COLUMNNAME,@col_desc=columndesc FROM @t1 WHERE rn=@i
            IF @overrideDesc=1
                begin
                    IF EXISTS(SELECT 1 FROM dc_util_column_desc WHERE tableName=@t_name AND columnName=@col_name)
                        UPDATE dc_util_column_desc SET columnDesc = @col_desc WHERE tableName=@t_name AND columnName=@col_name
                    ELSE
                        INSERT INTO dc_util_column_desc(tablename,columnName,columnDesc) VALUES (@t_name,@col_name,@col_desc)
                END
            ELSE
                BEGIN
                    IF NOT EXISTS(SELECT 1 FROM dc_util_column_desc WHERE tableName=@t_name AND columnName=@col_name )
                        INSERT INTO dc_util_column_desc(tablename,columnName,columnDesc) VALUES (@t_name,@col_name,@col_desc)
                END
            set @i=@i+1
        END
END
GO
--2.4 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_SetDescToTable]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_Util_Desc_SetDescToTable]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将 dc_util_table_desc 表中的 tableDesc 写到对应表的扩展属性
--              @tablePrefix 为表前缀 如果为 '' 或者 null, 则为全部表(默认为null)
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Util_Desc_SetDescToTable] 
    @tablePrefix varchar(100) = null
AS
BEGIN
    SET NOCOUNT ON
    --删除表中无效的数据
    exec Proc_Util_Desc_DeleteInvalidData
     
    --定义表变量
    DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))
    --插入需要修改扩展属性的数据到表变量@t1
    INSERT INTO @t1
    (
        tablename,
        tabledesc
    )
    SELECT tablename,tabledesc FROM dc_util_table_desc 
    WHERE ISNULL(@tablePrefix,'')='' OR tablename LIKE +@tablePrefix+'%'
    --循环表变量中的数据
    DECLARE @i INT
    DECLARE @i_max INT
    DECLARE @t_name VARCHAR(100)
    DECLARE @t_desc NVARCHAR(200)
    SET @i=1
    SELECT @i_max=COUNT(1) FROM @t1
    WHILE @i<=@i_max
        BEGIN
            SELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@i
            IF isnull(@t_desc,'')=''
            BEGIN
                SET @i=@i+1
                CONTINUE
            END
            --如果表上存在MS_Description就update,不存在就insert
            IF EXISTS (SELECT p.value
                        FROM   sys.tables                         AS t
                               LEFT JOIN sys.extended_properties  AS p
                                    ON  p.major_id = t.object_id
                        WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID
                                               FROM   sys.schemas
                                               WHERE  NAME = 'dbo')
                        AND     p.minor_id = 0
                        AND     p.class = 1
                        AND     p.name = 'MS_Description'
                        AND     t.name =@t_name)
                BEGIN
                    EXEC sp_updateextendedproperty 
                            @name = N'MS_Description'
                            ,@value = @t_desc
                            ,@level0type = N'Schema', @level0name = 'dbo'
                            ,@level1type = N'Table',  @level1name = @t_name
                END
            ELSE
                BEGIN
                        EXEC sp_addextendedproperty 
                            @name = N'MS_Description'
                            ,@value = @t_desc
                            ,@level0type = N'Schema', @level0name = 'dbo'
                            ,@level1type = N'Table',  @level1name = @t_name
                END
            SET @i=@i+1
        END
END
GO
--2.5 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_SetDescToColumn]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_Util_Desc_SetDescToColumn]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将dc_util_column_desc 表中的 columnDesc 写到对应表对应列的扩展属性
--              @tablePrefix 为表前缀 如果为 '' 或者 null, 则为全部表(默认为null)
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Util_Desc_SetDescToColumn] 
    @tablePrefix varchar(100) = null
AS
BEGIN
    SET NOCOUNT ON
    --删除表中无效的数据
    exec Proc_Util_Desc_DeleteInvalidData
     
    --定义表变量
    DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),columnname VARCHAR(100),columndesc NVARCHAR(200))
    -- 插入需要修改扩展属性的数据到表变量@t1
    INSERT INTO @t1
    (
        tablename,
        columnname,
        columndesc
    )
    SELECT tablename,columnname,columndesc FROM dc_util_column_desc 
    WHERE ISNULL(@tablePrefix,'')='' or tablename LIKE +@tablePrefix+'%'
 
    --循环表变量中的数据
    DECLARE @i INT
    DECLARE @i_max INT
    DECLARE @t_name VARCHAR(100)
    DECLARE @col_name VARCHAR(100)
    DECLARE @col_desc NVARCHAR(200)
    SET @i=1
    SELECT @i_max=COUNT(1) FROM @t1
    WHILE @i<=@i_max
        BEGIN
            SELECT @t_name=tablename,@col_name=columnname,@col_desc=columndesc FROM @t1 WHERE rn=@i
             
            IF ISNULL(@col_desc,'')=''
            BEGIN
                SET @i=@i+1
                CONTINUE
            END
             
            --如果列上存在MS_Description就update,不存在就add
            IF EXISTS (SELECT  p.value
                        FROM   sys.tables AS t
                               LEFT JOIN sys.extended_properties AS p ON  p.major_id = t.object_id
                               LEFT JOIN sys.columns c ON t.object_id=c.object_id AND c.column_id=p.minor_id
                        WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID
                                               FROM   sys.schemas
                                               WHERE  NAME = 'dbo')
                               AND p.class = 1
                               AND p.minor_id!=0
                               AND p.name = 'MS_Description'
                               AND t.name = @t_name
                               AND c.name = @col_name)
                BEGIN
                    EXEC sp_updateextendedproperty 
                            @name = N'MS_Description'
                            ,@value = @col_desc
                            ,@level0type = N'Schema', @level0name = 'dbo'
                            ,@level1type = N'Table',  @level1name = @t_name
                            ,@level2type = N'Column', @level2name = @col_name
                END
            ELSE
                BEGIN
                        EXEC sp_addextendedproperty 
                            @name = N'MS_Description'
                            ,@value = @col_desc
                            ,@level0type = N'Schema', @level0name = 'dbo'
                            ,@level1type = N'Table',  @level1name = @t_name
                            ,@level2type = N'Column', @level2name = @col_name
                END
            SET @i=@i+1
        END
END
GO
--3.1 触发器 
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trig_dc_util_table_desc_I_U]'))
    DROP TRIGGER [dbo].[Trig_dc_util_table_desc_I_U]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将记录更新到对应表的扩展属性
-- =============================================
CREATE TRIGGER [dbo].[Trig_dc_util_table_desc_I_U]
   ON [dbo].[dc_util_table_desc]
   AFTER INSERT , UPDATE
AS
BEGIN
    --触发Proc_Util_SetDescToTable 更新表描述
    DECLARE @m VARCHAR(100)
    SELECT @m=tablename FROM inserted
    EXEC Proc_Util_Desc_SetDescToTable @tablePrefix=@m
END
GO


--3.2 触发器
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trig_dc_util_column_desc_I_U]'))
    DROP TRIGGER [dbo].[Trig_dc_util_column_desc_I_U]
GO
-- =============================================
-- Author:      
-- Create date: 2014-05-29
-- Description: 将记录更新到对应列的扩展属性
-- =============================================
CREATE TRIGGER [dbo].[Trig_dc_util_column_desc_I_U]
   ON [dbo].[dc_util_column_desc]
   AFTER INSERT , UPDATE
AS
BEGIN
    --触发Proc_Util_SetDescToColumn 去更新列描述
    DECLARE @m VARCHAR(100)
    SELECT @m=tablename FROM inserted
    EXEC Proc_Util_Desc_SetDescToColumn @tablePrefix=@m
END
GO 


--4.1 查看表的描述
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_GetTableStru]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[Fun_GetTableStru]
GO
-- =============================================
-- Author:      
-- Create date: 2014-03-27
-- Description: 获取表结构
-- Demo: select * from [dbo].[Fun_GetTableStru]('表名')
-- =============================================
CREATE FUNCTION [dbo].[Fun_GetTableStru] 
(   
    @tableName NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
     
SELECT
    ac.column_id AS columnId
    ,AC.[name] AS columnName 
    ,TY.[name] AS dataType
    ,AC.max_length AS maxLength
    ,AC.[is_nullable] isNullable
    ,CASE WHEN AC.[name] in
        (SELECT COLUMN_NAME = convert(sysname,c.name) from sysindexes i, syscolumns c, sysobjects o where o.id = object_id(@tableName)
            and o.id = c.id
            and o.id = i.id
            and (i.status & 0x800) = 0x800
            and (
            c.name = index_col (@tableName, i.indid,  1) or
            c.name = index_col (@tableName, i.indid,  2) or
            c.name = index_col (@tableName, i.indid,  3) or
            c.name = index_col (@tableName, i.indid,  4) or
            c.name = index_col (@tableName, i.indid,  5) or
            c.name = index_col (@tableName, i.indid,  6) or
            c.name = index_col (@tableName, i.indid,  7) or
            c.name = index_col (@tableName, i.indid,  8) or
            c.name = index_col (@tableName, i.indid,  9) or
            c.name = index_col (@tableName, i.indid, 10) or
            c.name = index_col (@tableName, i.indid, 11) or
            c.name = index_col (@tableName, i.indid, 12) or
            c.name = index_col (@tableName, i.indid, 13) or
            c.name = index_col (@tableName, i.indid, 14) or
            c.name = index_col (@tableName, i.indid, 15) or
            c.name = index_col (@tableName, i.indid, 16)
            )) THEN 1 ELSE 0 END AS isPK
    ,CASE WHEN AC.[name] IN (  
            SELECT t1.name 
            FROM   (  
                       SELECT col.name,  
                              f.constid       AS temp 
                       FROM   syscolumns col,  
                              sysforeignkeys     f  
                       WHERE  f.fkeyid = col.id  
                              AND f.fkey = col.colid  
                              AND f.constid IN (SELECT DISTINCT(id)  
                                                FROM   sysobjects  
                                                WHERE  OBJECT_NAME(parent_obj) =   
                                                       @tableName  
                                                       AND xtype = 'F')  
                   )  AS t1,  
                   (  
                       SELECT OBJECT_NAME(f.rkeyid)  AS rtableName,  
                              col.name,  
                              f.constid              AS temp 
                       FROM   syscolumns col,  
                              sysforeignkeys            f  
                       WHERE  f.rkeyid = col.id  
                              AND f.rkey = col.colid  
                              AND f.constid IN (SELECT DISTINCT(id)  
                                                FROM   sysobjects  
                                                WHERE  OBJECT_NAME(parent_obj) =   
                                                       @tableName  
                                                       AND xtype = 'F')  
                   )  AS t2  
            WHERE  t1.temp = t2.temp 
    ) THEN 1 ELSE 0 END AS isFK
    ,(SELECT COLUMNPROPERTY( OBJECT_ID(@tableName),ac.name,'IsIdentity')) AS isIdentity 
    ,ISNULL(t2.[DESCRIPTION], '') AS [columnDesc]
    ,ISNULL((
        SELECT ISNULL(VALUE, '')
       FROM   sys.extended_properties ex_p
       WHERE  ex_p.minor_id = 0
              AND ex_p.major_id = t.OBJECT_ID
    ),'') AS [tableDesc]
FROM    sys.[tables] AS T
        INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
        INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
                                     AND AC.[user_type_id] = TY.[user_type_id]
        LEFT JOIN (  
                    SELECT DISTINCT(sys.columns.name),  
                           (  
                               SELECT VALUE  
                               FROM   sys.extended_properties  
                               WHERE  sys.extended_properties.major_id = sys.columns.object_id  
                                      AND sys.extended_properties.minor_id = sys.columns.column_id  
                           ) AS DESCRIPTION  
                    FROM   sys.columns,  
                           sys.tables,  
                           sys.types  
                    WHERE  sys.columns.object_id = sys.tables.object_id  
                           AND sys.columns.system_type_id = sys.types.system_type_id  
                           AND sys.tables.name = @tableName  
                ) AS t2  ON AC.name=t2.name
WHERE   T.[is_ms_shipped] = 0 AND T.name=@tableName
)
GO

转自:http://www.2cto.com/database/201405/305287.html
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:270287次
    • 积分:3449
    • 等级:
    • 排名:第10020名
    • 原创:53篇
    • 转载:152篇
    • 译文:0篇
    • 评论:23条
    最新评论