Handling Column's MS Description

Handling Column's MS Description

I was requested to create a tool for database documenting. The idea was to allow the customer QA to use the extended property MS Description of MS SQL server (2005,2008) to add details on each column. The customer didn’t want to allow the QA to enter the table design view and we had to develop a small external application (with the cool name DDT, Database Documenting Tool).

The time frame for this app was 9 hours, from them it took me 3 hours of googling, msdn searching and just playing around because there isn’t actually one place that summarize this issue. So this is my small contribution. At the end the app looks like this (I use Krypton controls for the design):

handling-columns-ms-description[1]

And these are the stored procedures and Selects that I used:

For the main tree control, (To get all the tables and fields):


SELECT table_id = Rank() OVER (ORDER BY table_name DESC),
*FROM (SELECT DISTINCT table_name = Object_name(c.object_id),column_name = ",column_id = -1,

column_description = ",

dtype = ",

is_nullable=0,

is_identity=0

FROM sys.columns c

WHERE Objectproperty(c.object_id, 'IsMsShipped') = 0

UNION

SELECT table_name = Object_name(c.object_id),

column_name = c.name,

column_id = c.column_id,

column_description = Isnull(ex.VALUE, "),

dtype = ty.name + CASE

WHEN c.user_type_id <> 231 THEN "

ELSE ' (' +

CONVERT(VARCHAR(10), Isnull(c.max_length, 0)) +

')'

END,

c.is_nullable,

is_identity

FROM sys.columns c

LEFT OUTER JOIN sys.extended_properties ex

ON ex.major_id = c.object_id

AND ex.minor_id = c.column_id

AND ex.name = 'MS_Description'

LEFT OUTER JOIN sys.types ty

ON ty.user_type_id = c.user_type_id

LEFT OUTER JOIN sys.tables tb

ON tb.object_id = c.object_id

AND tb.TYPE = 'U'

WHERE Objectproperty(c.object_id, 'IsMsShipped') = 0)tt

ORDER BY table_name,

column_id

When clicking the Table name:


SELECT [Key?] = CASE
WHEN c.is_identity = 1 THEN 'Y'ELSE "END,[Is Must?] = CASE

WHEN c.is_identity = 1 THEN 'Y'

ELSE CASE

WHEN Isnull(c.is_nullable, 0) = 0 THEN "

ELSE 'Y'

END

END,

TYPE = ty.name + CASE

WHEN c.user_type_id <> 231 THEN "

ELSE ' (' +

CONVERT(VARCHAR(10), Isnull(c.max_length, 0)) +

')'

END,

name = c.name,

DESCRIPTION = Isnull(ex.VALUE, "),

table_name = Object_name(c.object_id)

FROM sys.columns c

LEFT OUTER JOIN sys.extended_properties ex

ON ex.major_id = c.object_id

AND ex.minor_id = c.column_id

AND ex.name = 'MS_Description'

LEFT OUTER JOIN sys.types ty

ON ty.user_type_id = c.user_type_id

WHERE Objectproperty(c.object_id, 'IsMsShipped') = 0

AND Object_name(c.object_id) = 'TableName'

ORDER BY column_id

SQL to update the MS Description Coulnm:


EXECUTE Usp_filldescriptions
'AUT_UserGroups','UserGroupName','User Goroup Name Description'

The stored procedure that update the MS Description (Not my code, credit to Michael Livshitz/C# Corner…)


ALTER PROCEDURE [dbo].[Usp_filldescriptions] (@Table NVARCHAR(50),
@Column NVARCHAR(50),@Descr NVARCHAR(50) = ",@Schema NVARCHAR(50) = 'dbo')AS

DECLARE @Select NVARCHAR (500)

DECLARE @Update NVARCHAR (500)

DECLARE @Add NVARCHAR (500)

DECLARE @All NVARCHAR (2000)

BEGINIF ( Ltrim(Rtrim(@Descr)) = " )

BEGIN

SET @Descr = @Column

END

SET @Select = ' SELECT name FROM ' +

' ::fn_listextendedproperty (default,"schema","' +

@Schema + "', "table", "' + @Table +

"', "column", "' +

@Column + "') where name="MS_DESCRIPTION" '

SET @Update = 'EXEC sp_updateextendedproperty "MS_Description","' +

@Descr +

"',"schema","' + @Schema +

"',"table","'

+

@Table +

"', "column","' + @Column + "' '

SET @Add = 'EXEC sp_addextendedproperty "MS_Description","' + @Descr +

"',"schema","' + @Schema + "',"table","' +

@Table

+

"',"column","' + @Column + "' '

SET @All = ' IF EXISTS (' + @Select + ') ' + ' begin ' + @Update + ' end '

+

' else ' +

' begin ' + @Add + ' end '

EXEC Sp_executesql @All

END

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

תגי HTML מותרים: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>