Using tables to manage extended attributes in SQL Server (descriptive narrative)

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

However, it is not convenient to write descriptive narration in SQL Server, how to simplify and add changes to the extended attributes in batches?

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

Run the following SQL once to generate the related object and then run it:

1. EXEC proc_util_desc_getcolumnnametodesctable, generate table descriptive narrative corresponding record

2. EXEC proc_util_desc_gettablenametodesctable, generating column descriptive narrative corresponding record

3. View, change 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 the 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 Building table (descriptive narrative of the storage column): [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] 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 [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 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--the wrong data in the Dc_util_column_desc table--=============================================create PROCE dure [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   _util_column_descwhere not EXISTS (SELECT 1 from sys.tables t INNER joins 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 D bo.dc_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 into the DC_UTIL_TABLE_DESC table the prefix table name and the corresponding extended property of the table.--@tablePrefix assumed to be "or null, all tables (default is null)-- @overrideDesc: If a record exists, overwrite the extended attribute of the original record (default 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 a table prefixed with @tablePrefix to @t1insert into @t1 (TABLENAME,TABLEDESC) SELECT convert (VARCHAR), t.name, convert (nvarchar ( P.value) from Sys.tables as T-left JOIN sys.extended_properties as PON p.major_id = T.ob Ject_idand p.minor_id = 0AND P.class = 1AND 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 [email protected]+ '% ') DECLARE @i intde CLARE @i_max intdeclare @t_name VARCHAR (1) DECLARE @t_desc NVARCHAR ($) SET @i=1select @i_max =count () from @t1WHILE @i& Lt [Email protected]_maxbeginselect @t_name =tablename, @t_desc =tabledesc from @t1 WHERE [Email protected]if @ Overridedesc=1beginif EXISTS (SELECT 1 fromDc_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] INSERT 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 corresponding extension attribute of the corresponding columns column for the table name of the prefix into the Dc_util_column_desc table.--@tablePrefix assumed to be ' ' or null, all tables (default is null)-- @overrideDesc: If a record exists, overwrite the extended attribute of the original record (default 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 (2XX) 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: DC The Tabledesc in the _util_table_desc table writes to the extended property of the corresponding table--@tablePrefix is assumed to be the table prefix "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 change the extended property 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--Assuming that there is ms_description on the table, the update does not exist on 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 From sys.schemas WHERE NAME = ' dbo ') andp.minor_id = 0andp.class = 1andp.name = ' ms_description ' Andt.name [email&nbsp ;p rotected]_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_ Description ', @value = @t_desc, @level0type = N ' Schema ', @level0name = ' dbo ', @level1type = N ' Table ', @level1name = @t_nameE Ndset @[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 column of the corresponding table-- @tablePrefix the table prefix is assumed to be ' ' or null, all tables (default is NULL)--=============================================create PROCEDURE [dbo]. [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 change the extended property to the 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--assumes a 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 Descriptive narrative 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.ind  ID, 1) orc.name = Index_col (@tableName, I.indid, 2) orc.name = Index_col (@tableName, I.indid, 3) Orc.name = Index_col (@tableName, I.indid, 4) Orc.name = Index_col (@tableName, I.indid, 5) Orc.name = Index_col (@tableName, I.indid, 6) O Rc.name = Index_col (@tableName, I.indid, 7) Orc.name = Index_col (@tableName, I.indid, 8) Orc.name = Index_col (@tableN  Ame, I.indid, 9) Orc.name = Index_col (@tableName, I.indid, ten) Orc.name = Index_col (@tableName, I.indid, one) Orc.name = Index_col (@tableName, I.indid,) Orc.name = Index_col (@tableName, I.indid,) Orc.name = Index_col (@tableName, i.in  did, Orc.name = Index_col (@tableName, I.indid, 1) orc.name = Index_col (@tableName, I.indid, +)) then ELSE 0 END As Ispk,case when Ac.[name] in (select T1.name from (select Col.nam                      E          F.constid as temp from syscolumns col, sysforeign                                Keys f WHERE F.fkeyid = col.id and F.fkey = Col.colid and F.constid in (the SELECT DISTINCT (ID) from SYSOB                                                          Jects 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 SYSC                           Olumns 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_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

Using tables to manage extended attributes in SQL Server (descriptive narrative)