Using tables to manage extended attributes in SQL Server (description)

Data dictionary is a good thing, for development, maintenance is very important.

However, it is not convenient to write the description in SQL Server, how to simplify and add the modified extended attributes in bulk?

It would be nice to add 2 tables and 5 stored procedures, 2 triggers, and a table-valued function.

Execute the following SQL once to generate the related object and then execute it:

1. EXEC proc_util_desc_getcolumnnametodesctable, generating the table description corresponding to the record

2. EXEC proc_util_desc_gettablenametodesctable, generating a description of the column corresponding to the record

3. View, modify the description of a column in a table in Dc_util_column_desc,

4. View: SELECT * FROM [dbo]. [Fun_gettablestru] (' Table name ')

Cool, huh?!

--1.1 table (Description of storage table): Dbo.dc_util_table_descif 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] Gocreate TABLE [dbo]. [Dc_util_table_desc] ([ID] [int] IDENTITY (max) not null,[tablename] [varchar] (+) null,[tabledesc] [nvarchar] (+) NULL, CONSTRAINT [pk_dc_ UTIL_TABLE_DESC] PRIMARY KEY CLUSTERED ([id] ASC) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = O FF, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]) on [primary]go--1.2 Build table (Description of storage column): [Dc_util_column_desc]if E Xists (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] Gocreate TABLE [dbo]. [Dc_util_column_desc] ([ID] [int] IDENTITY (max) not null,[tablename] [varchar] (+) null,[columnname] [varchar] (+) NULL,[COLUMNDESC] [ NVARCHAR] (+) 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 [PRI MARY], 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 = O n) on [PRIMARY]) on [primary]go--2.1 stored procedure 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: Delete dc_ Util_table_desc table and--incorrect data in the Dc_util_column_desc table--=============================================create PROC edure [dbo]. [Proc_util_desc_deleteinvaliddata] Asbeginset NOCOUNT on;--Delete invalid data in Dc_util_table_desc Delete from Dbo.dc_util_table_desc WHERE isn't EXISTS (SELECT 1 from sys .Tables T WHERE dbo.dc_util_table_desc.tablename=t.name)--Delete invalid data in Dc_util_column_desc delete from Dbo.dc_ut Il_column_descwhere not EXISTS (SELECT 1 from sys.tables t INNER JOIN sys.columns c on t.object_id = C.object_idwhere t . schema_id in (SELECT schema_id from sys.schemas WHERE NAME = ' dbo ') and Dbo.dc_util_column_desc.tablename=t.name and DBO.D c_util_column_desc.columnname=c.name) endgo--2.2 Stored procedure 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: will be @tablePrefix              Insert the extended attribute of the table name and table corresponding to the prefix into the Dc_util_table_desc table.--@tablePrefix if "or null, the entire table (default is null)-- @overrideDesc: If an existing record exists, overwrite the extended attribute of the original record (default is 1)-=============================================create procedure [dbo]. [Proc_util_desc_gettabLenametodesctable] @tablePrefix VARCHAR (=null), @overrideDesc BIT =1as beginset NOCOUNT on--Delete invalid data in table exec Proc_util_ Desc_deleteinvaliddatadeclare @t1 TABLE (rn int IDENTITY, tablename VARCHAR), Tabledesc NVARCHAR (200))--Insert at @ Tableprefix-prefixed table to @t1insert into @t1 (TABLENAME,TABLEDESC) SELECT convert (VARCHAR (+), t.name), convert (nvarchar (200) , P.value) from Sys.tables as T-left JOIN sys.extended_properties as PON p.major_id = T.object _idand p.minor_id = 0AND P.class = 1AND p.name = ' ms_description ' WHERE t.schema_id in (SELECT schema_id from Sys.sche Mas WHERE NAME = ' dbo ') and (ISNULL (@tablePrefix, ') = ' or t.name like [email protected]+ '% ') DECLARE @i intdeclar E @i_max intdeclare @t_name VARCHAR (DECLARE) @t_desc NVARCHAR SET @i=1select @i_max =count (1) from @t1WHILE @i<[ Email protected]_maxbeginselect @t_name =tablename, @t_desc =tabledesc from @t1 WHERE [Email protected]if @ Overridedesc=1beginif EXISTS (SELECT 1 from Dc_Util_table_desc where [email protected]_name) UPDATE dc_util_table_desc SET tabledesc = @t_desc where [email  Protected]_nameelse INSERT into Dc_util_table_desc (TABLENAME,TABLEDESC) VALUES (@t_name, @t_desc) Endelse beginif not EXISTS (SELECT 1 from Dc_util_table_desc WHERE [email protected]_name) inserts into DC_UTIL_TABLE_DESC (tablename, TABLEDESC) VALUES (@t_name, @t_desc) endset @[email protected]+1endendgo--2.3 stored procedure 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: will be @tablePrefix              Insert the extension attribute for the columns column corresponding to the table name of the prefix into the Dc_util_column_desc table.--@tablePrefix if "or null, the entire table (default is null)-- @overrideDesc: If an existing record exists, overwrite the extended attribute of the original record (default is 1)-=============================================create procedure [dbo].[Proc_util_desc_getcolumnnametodesctable] @tablePrefix VARCHAR (=null), @overrideDesc BIT =1as beginset NOCOUNT on--Delete invalid data in table exec proc_util_desc_deleteinvaliddatadeclare @t1 table (rn int IDENTITY (), TableName VARCHAR (100), COLUMNNAME VARCHAR (+), Columndesc NVARCHAR (200))--insert a table prefixed with @tablePrefix to the @t1insert into @t1 (Tablename,columnname, COLUMNDESC) SELECT CONVERT (varchar (+), t.name), convert (varchar (+), c.name), convert (nvarchar (P.value) from Sys.tables as T-left JOIN sys.columns cON t.object_id = c.object_id LEF T JOIN sys.extended_properties as PON p.major_id = T.object_idand p.minor_id = C.column_idand P.class = 1AND p.name = ' M S_description ' where t.schema_id in (SELECT schema_id from sys.schemas WHERE NAME = ' dbo ') and (ISNULL (@tablePrefix , ') = ' or t.name like [email protected]+ '% ') DECLARE @i intdeclare @i_max intdeclare @t_name VARCHAR (+) DECLARE @co L_name VARCHAR (+) DECLARE @col_desc NVARCHAR (200)SET @i=1select @i_max =count (1) from @t1WHILE @i<[email protected]_maxbeginselect @t_name =tablename, @col_name =columnname, @col_desc =columndesc from @t1 WHERE [email protected]if @overrideDesc =1beginif EXISTS (SELECT 1 from DC _util_column_desc WHERE [Email protected]_name and [Email protected]_name] UPDATE Dc_util_column_desc SET Columndesc = @col_desc WHERE [Email protected]_name and [Email protected]_nameelse INSERT into Dc_util_column _desc (TABLENAME,COLUMNNAME,COLUMNDESC) VALUES (@t_name, @col_name, @col_desc) endelse beginif not EXISTS (SELECT 1 from DC _util_column_desc WHERE [Email protected]_name and [Email protected]_name] INSERT into Dc_util_column_desc ( TABLENAME,COLUMNNAME,COLUMNDESC) VALUES (@t_name, @col_name, @col_desc) Endset @[email protected]+ 1endendgo--2.4 stored Procedure 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: Will Dc_ Util_table_desc table Tabledesc Write to the extended property of the corresponding table--@tablePrefix table prefix if "or null, all tables (default is NULL)--=========================== ==================create PROCEDURE [dbo]. [proc_util_desc_setdesctotable] @tablePrefix varchar = nullasbeginset NOCOUNT on--Delete invalid data in table exec PROC_UTIL_DESC_ deleteinvaliddata--Define table variable declare @t1 table (rn int IDENTITY (), TableName VARCHAR, Tabledesc NVARCHAR (200))-- Insert data that needs to modify extended properties to table variable @t1insert into @t1 (TABLENAME,TABLEDESC) SELECT Tablename,tabledesc from Dc_util_table_desc WHERE ISNULL (@tablePrefix, ') = ' OR tablename like [email protected]+ '% '--data in the Loop table variable declare @i intdeclare @i_max intDECLARE @t_name VARCHAR DECLARE @t_desc NVARCHAR SET @i=1select @i_max =count (1) from @t1WHILE @i<[email& Nbsp;protected]_maxbeginselect @t_name =tablename, @t_desc =tabledesc from @t1 WHERE [Email protected]if isnull (@t _desc, ') = ' Beginset @[email protected]+1continueend--If there is ms_description on the table, update does not exist Insertif EXISTS (SELECT p.valuefrom sys.tables As T left JOIN sys.extended_properties as PON p.major_id = T.object_idwhere t.schema_id in (SELECT schema_id FR OM sys.schemas WHERE NAME = ' dbo ') andp.minor_id = 0andp.class = 1andp.name = ' ms_description ' Andt.name [EMAIL&NBSP;PR Otected]_name) beginexec Sp_updateextendedproperty @name = n ' ms_description ', @value = @t_desc, @level0type = N ' Schema ', @ Level0name = ' dbo ', @level1type = N ' Table ', @level1name = @t_nameENDELSEBEGINEXEC sp_addextendedproperty @name = n ' ms_desc Ription ', @value = @t_desc, @level0type = N ' Schema ', @level0name = ' dbo ', @level1type = N ' Table ', @level1name = @t_nameENDSE T @[email protected]+1endendgo--2.5 Stored Procedure 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: Writes Dc_util_column_desc in the Columndesc table to the extended property of the corresponding table column-- @tablePrefix is the table prefix if ' or null ', all tables (default is NULL)--=============================================create PROCEDURE [db O]. [Proc_util_desc_setdesctocolumn] @tablePrefix varchar = nullasbeginset NOCOUNT on--Delete invalid data in table exec PROC_UTIL_DESC_ deleteinvaliddata--defines the table variable declare @t1 table (rn int IDENTITY (tablename), ColumnName varchar (100), Columndesc NVARCHAR (200))--insert data that needs to modify extended properties to table variable @t1insert into @t1 (TABLENAME,COLUMNNAME,COLUMNDESC) SELECT TableName, Columnname,columndesc from Dc_util_column_desc WHERE ISNULL (@tablePrefix, ") = ' or tablename like [email  protected]+ '% '--data in the Loop table variable DECLARE @i intdeclare @i_max intdeclare @t_name varchar DECLARE @col_name varchar (100) DECLARE @col_desc NVARCHAR ($) SET @i=1select @i_max =count (1) from @t1WHILE @i<[email protected]_ Maxbeginselect @t_name =tablename, @col_name =columnname, @col_desc =columndesc from @t1 where [Email protected]if ISNULL (@col_desc, ') = ' Beginset @[email protected]+1continueend--if the column exists Ms_ Description on UPDATE, there is no addif EXISTS (SELECT p.valuefrom sys.tables as T left joins 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_idwhere T.SCHEMA_ID I N (SELECT schema_id from sys.schemas WHERE NAME = ' dbo ') and P.class = 1 and p.minor_id!=0 and p.name = ' ms_d Escription ' and t.name = @t_name and c.name = @col_name) beginexec 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_nameENDELSEBEGINEXEC sp_addextendedproperty @name = n ' ms_description ', @value = @col_desc , @level0type = N ' Schema ', @level0name = ' dbo ', @level1type = N ' Table ', @level1name = @t_name, @level2type = N ' Column ', @lev El2name = @col_nameENDSET @[email protected]+1endendgo--3.1 Trigger 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: Update the record to the extended property of the corresponding table--=============================================create TRIGGER [dbo].   [Trig_dc_util_table_desc_i_u] on [dbo].   [Dc_util_table_desc] After INSERT, Updateas begin--trigger proc_util_setdesctotable Update table Description declare @m VARCHAR (+) SELECT @m=tablename from Insertedexec proc_util_desc_setdesctotable @[email protected]end--3.2 Trigger 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: Update the record to the extended property of the corresponding column--=============================================create TRIGGER [dbo].   [Trig_dc_util_column_desc_i_u] ON [dbo].   [Dc_util_column_desc] After INSERT, Updateas begin--trigger Proc_util_setdesctocolumn to update column description declare @m VARCHAR (+) SELECT @m=tablename from Insertedexec proc_util_desc_setdesctocolumn @[email protected]end--4.1 View table description 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: Get Table Structure-Demo: SELECT * FROM [dbo]. [Fun_gettablestru] (' Table name ')--=============================================create FUNCTION [dbo]. [Fun_gettablestru] (@tableName NVARCHAR (MAX)) RETURNS TABLE Asreturn (selectac.column_id as ColumnId, Ac.[name] as ColumnName, ty.[ Name] as DataType, ac.max_length as MaxLength, ac.[is_nullable] isnullable, case if 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.idand o.id = I.idand (I.status & 0x800) = 0x800and (C.name = Index_col (@tableName, I.indid, 1) o Rc.name = Index_col (@tableName, I.indid, 2) orc.name = Index_col (@tableName, I.indid, 3) Orc.name = Index_col (@tableN  Ame, I.indid, 4) Orc.name = Index_col (@tableName, I.indid, 5) Orc.name = Index_col (@tableName, I.indid, 6) Orc.name = Index_col (@tableName, I.indid, 7) Orc.name = Index_col (@tableName, I.indid, 8) Orc.name = Index_col (@tableName, i.in did, 9) Orc.name = Index_col (@tableName, I.indid, ten) Orc.name = Index_col (@tableName, I.indid, one) Orc.name = Index_co L (@tableName, I.indid, n) orc.name = Index_col (@tableName, i.indid, orc.name = Index_col (@tableName, I.indid, 14) Orc.name = Index_col (@tableName, I.indid, 1) orc.name = Index_col (@tableName, I.indid,)) then ELSE 0 END as ISPK,                               Case if 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 (the SELECT DISTINCT (id) from sysobjects                                                          WHERE object_name (parent_obj) = @tableName and xtype = ' F ')) A                                S T1, (SELECT object_name (F.rkeyid) as Rtablename, Col.name, F.constid as temp from syscolumns Co                                L, Sysforeignkeys f WHERE F.rkeyid = Col.id andF.rkey = Col.colid and F.constid in (SELECT DISTINCT (ID)                                                          From sysobjects WHERE object_name (parent_obj) = @tableName and X Type = ' F ')) as t2 WHERE t1.temp = t2.temp) then 1 ELSE 0 END as ISFK, (Selec T 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_properti                             es.minor_id = sys.columns.column_id) as DESCRIPTION from Sys.columns, Sys.tables, sys.types where Sys.columns.obje                             ct_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.namewhere t.[is_ms_shipped ] = 0 and [email protected]) GO