Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.436 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • October 29th, 2017
  • 6
  • Database Query Development DMVs and Catalog Views Tools Little Known Features SQL Server

SQL Server - How to document the database and its objects (tables, procedures, columns) using Extended Property

Views: 5.726
Reading Time: 19 minutes

Hello everybody!
All right with you ?

In this post I would like to comment on something very important during the day-to-day of DBA's and Query Developers, which is the database documentation. I hardly see environments where the columns or tables have a clear description of what this bank object is about.

Introduction

For those who create queries all day long, such as BI analysts, this information makes understanding queries and the accuracy of information much easier. To enter this information in the database, we will use a very old feature of SQL Server, but few people use or even know, which is the Extended Property.

Using system procedures, we can describe database objects using their own words to make it easier for others to understand.

Once these descriptions are entered into the database, you can use tools to view this information as you develop your queries or even to generate complete database documentation from Extended Property descriptions.

Unlike some database documentation tools, this feature stores descriptions in the database itself, so that the time taken to register these descriptions is not lost if you intend to change the documentation tool, otherwise these descriptions will be saved. in a safe place with backups (your bank has backup right ?!).

How to document SQL Server databases

For the documentation of SQL Server databases, we will use the feature called Extended Property and the system procedures. sp_addextendedproperty, sp_updateextendedproperty e sp_dropextendedproperty.

In order to use these procedures, the user must be in database roles db_owner or ddl_admin (this role does not allow you to add descriptions for the database itself, users or roles) or have the ALTER / CONTROL privilege on the objects they want to add. the descriptions. And of course, server role users like sysadmin can also use these procedures.

The types of objects that can be documented using Extended Property using these system SP's (@level1type) are: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, and XML SCHEMA COLLECTION.

The subtypes of objects that can be documented (@level2type) are: COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER and TRIGGER. Subtypes (@ level2type) depend on the type (@ level1type) to define them, for example, to document a column, where you must define @ level1type = 'TABLE' and @ level2type = 'COLUMN', referring to which table that column is part of.

In order to facilitate the use of these procedures, I will provide here some procedures that will check if the object in question already has Extended Property and, if it has, uses the sp_updateextendedproperty or if not, uses the sp_addextendedproperty.

stpExtendedProperty_Table

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
CREATE PROCEDURE [dbo].[stpExtendedProperty_Tabela] (
    @Ds_Database sysname,
    @Ds_Tabela sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a tabela "' + @Ds_Tabela + '" no database "' + @Ds_Database + '"'
        RETURN
    END
 
 
    
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.objects B ON A.major_id = B.object_id WHERE A.class = 1 AND A.name = ''MS_Description'' AND B.name = ''' + @Ds_Tabela + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @Ds_Tabela + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''TABLE'',
                @level1name = ''' + @Ds_Tabela + '''
                    
        END
        ELSE
            PRINT ''A Tabela "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_Tabela + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''TABLE'',
            @level1name = ''' + @Ds_Tabela + '''
        
    END
    '
    
    BEGIN TRY
        
        EXEC(@query)
    
    END TRY
    
    BEGIN CATCH
 
        PRINT @query
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_Column

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CREATE PROCEDURE [dbo].[stpExtendedProperty_Coluna] (
    @Ds_Database sysname,
    @Ds_Tabela sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Coluna sysname,
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a coluna "' + @Ds_Coluna + '" da tabela "' + @Ds_Tabela + '" no database "' + @Ds_Database + '"'
        RETURN
    END
 
 
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.objects B ON A.major_id = B.object_id JOIN [' + @Ds_Database + '].sys.columns C ON B.id = C.object_id AND A.minor_id = C.column_id WHERE A.class = 1 AND A.minor_id > 0 AND A.name = ''MS_Description'' AND B.name = ''' + @Ds_Tabela + ''' AND C.name = ''' + @Ds_Coluna + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @Ds_Tabela + ''' AND COLUMN_NAME = ''' + @Ds_Coluna + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''TABLE'',
                @level1name = ''' + @Ds_Tabela + ''',
                @level2type = N''COLUMN'',
                @level2name = ''' + @Ds_Coluna + '''
                    
        END
        ELSE
            PRINT ''A coluna "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_Tabela + '.' + @Ds_Coluna + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''TABLE'',
            @level1name = ''' + @Ds_Tabela + ''',
            @level2type = N''COLUMN'',
            @level2name = ''' + @Ds_Coluna + '''
        
    END
    '
 
    
    BEGIN TRY
    
        EXEC(@query)
    
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_Trigger

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
CREATE PROCEDURE [dbo].[stpExtendedProperty_Trigger] (
    @Ds_Database sysname,
    @Ds_Tabela sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Trigger sysname,
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a trigger "' + @Ds_Trigger + '" da tabela "' + @Ds_Tabela + '" no database "' + @Ds_Database + '"'
        RETURN
    END
 
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.triggers B ON A.major_id = B.object_id JOIN [' + @Ds_Database + '].sys.objects C ON B.parent_id = C.object_id WHERE A.class = 1 AND A.name = ''MS_Description'' AND C.name = ''' + @Ds_Tabela + ''' AND B.name = ''' + @Ds_Trigger + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.triggers A JOIN [' + @Ds_Database + '].sys.objects B ON A.parent_id = B.object_id WHERE B.name = ''' + @Ds_Tabela + ''' AND A.name = ''' + @Ds_Trigger + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''TABLE'',
                @level1name = ''' + @Ds_Tabela + ''',
                @level2type = N''TRIGGER'',
                @level2name = ''' + @Ds_Trigger + '''
                    
        END
        ELSE
            PRINT ''A trigger "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_Tabela + '.' + @Ds_Trigger + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''TABLE'',
            @level1name = ''' + @Ds_Tabela + ''',
            @level2type = N''TRIGGER'',
            @level2name = ''' + @Ds_Trigger + '''
        
    END
    '
 
    
    BEGIN TRY
    
        EXEC(@query)
    
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_View

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE PROCEDURE [dbo].[stpExtendedProperty_View] (
    @Ds_Database sysname,
    @Ds_View sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a view "' + @Ds_View + '" no database "' + @Ds_Database + '"'
        RETURN
    END
        
        
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.objects B ON A.major_id = B.object_id WHERE A.class = 1 AND A.name = ''MS_Description'' AND B.name = ''' + @Ds_View + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ''' + @Ds_View + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''VIEW'',
                @level1name = ''' + @Ds_View + '''
                    
        END
        ELSE
            PRINT ''A View "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_View + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''VIEW'',
            @level1name = ''' + @Ds_View + '''
        
    END
    '
 
    BEGIN TRY
    
        EXEC(@query)
        
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
 
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_Procedure

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE PROCEDURE [dbo].[stpExtendedProperty_Procedure] (
    @Ds_Database sysname,
    @Ds_Procedure sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a Stored Procedure "' + @Ds_Procedure + '" no database "' + @Ds_Database + '"'
        RETURN
    END
        
        
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.objects B ON A.major_id = B.object_id WHERE A.class = 1 AND A.name = ''MS_Description'' AND B.name = ''' + @Ds_Procedure + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ''' + @Ds_Procedure + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''PROCEDURE'',
                @level1name = ''' + @Ds_Procedure + '''
                    
        END
        ELSE
            PRINT ''A Stored Procedure "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_Procedure + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''PROCEDURE'',
            @level1name = ''' + @Ds_Procedure + '''
        
    END
    '
 
    BEGIN TRY
    
        EXEC(@query)
        
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
 
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_Function

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE PROCEDURE [dbo].[stpExtendedProperty_Function] (
    @Ds_Database sysname,
    @Ds_Function sysname,
    @Ds_Schema sysname = 'dbo',
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
    
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a Function "' + @Ds_Function + '" no database "' + @Ds_Database + '"'
        RETURN
    END
        
        
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.objects B ON A.major_id = B.object_id WHERE A.class = 1 AND A.name = ''MS_Description'' AND B.name = ''' + @Ds_Function + ''') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ''' + @Ds_Function + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''SCHEMA'',
                @level0name = ''' + @Ds_Schema + ''',
                @level1type = N''FUNCTION'',
                @level1name = ''' + @Ds_Function + '''
                    
        END
        ELSE
            PRINT ''A function "' + @Ds_Database + '.' + @Ds_Schema + '.' + @Ds_Function + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + ''',
            @level0type = N''SCHEMA'',
            @level0name = ''' + @Ds_Schema + ''',
            @level1type = N''FUNCTION'',
            @level1name = ''' + @Ds_Function + '''
        
    END
    '
 
    BEGIN TRY
    
        EXEC(@query)
        
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
 
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_User

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
CREATE PROCEDURE [dbo].[stpExtendedProperty_Usuario] (
    @Ds_Database sysname = NULL,
    @Ds_Usuario sysname,
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
 
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para o usuário "' + @Ds_Usuario + '" no database "' + @Ds_Database + '"'
        RETURN
    END
 
 
 
    DECLARE @query VARCHAR(MAX)
        
        
    IF (NULLIF(LTRIM(RTRIM(@Ds_Database)), '') IS NOT NULL)
    BEGIN
        
        SET @query = '
            
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A JOIN [' + @Ds_Database + '].sys.database_principals B ON A.major_id = B.principal_id AND A.class = 4 WHERE A.name = ''MS_Description'' AND B.name = ''' + @Ds_Usuario + ''') = 0)
        BEGIN
            
            IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.database_principals WHERE name = ''' + @Ds_Usuario + ''') > 0)
            BEGIN
            
                EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                    @name = N''MS_Description'',
                    @value = ''' + @Ds_Texto + ''',
                    @level0type = N''USER'',
                    @level0name = N''' + @Ds_Usuario + '''
                        
            END
            ELSE
                PRINT ''O usuário "' + @Ds_Usuario + ' não existe no database "' + @Ds_Database + '" para adicionar ExtendedProperty.''
                    
        END
        ELSE BEGIN
                
            EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + ''',
                @level0type = N''USER'',
                @level0name = N''' + @Ds_Usuario + ''';
            
        END
        '
            
    END
    ELSE BEGIN
        
        SET @query = '
            
        IF ((SELECT COUNT(*) FROM [?].sys.extended_properties A JOIN [?].sys.database_principals B ON A.major_id = B.principal_id AND A.class = 4 WHERE A.name = ''MS_Description'' AND B.name = ''' + @Ds_Usuario + ''') = 0)
        BEGIN
            
            IF ((SELECT COUNT(*) FROM [?].sys.database_principals WHERE name = ''' + @Ds_Usuario + ''') > 0)
            BEGIN
            
                EXEC [?].sys.sp_addextendedproperty
                    @name = N''MS_Description'',
                    @value = ''' + @Ds_Texto + ''',
                    @level0type = N''USER'',
                    @level0name = N''' + @Ds_Usuario + '''
                        
            END
                    
        END
        ELSE BEGIN
            
            IF ((SELECT COUNT(*) FROM [?].sys.database_principals WHERE name = ''' + @Ds_Usuario + ''') > 0)
            BEGIN
                
                EXEC [?].sys.sp_updateextendedproperty
                    @name = N''MS_Description'',
                    @value = ''' + @Ds_Texto + ''',
                    @level0type = N''USER'',
                    @level0name = N''' + @Ds_Usuario + ''';
                        
            END
            
        END
        '
        
    END
 
 
    BEGIN TRY
    
        IF (NULLIF(LTRIM(RTRIM(@Ds_Database)), '') IS NOT NULL)
            EXEC(@query)    
        ELSE
            EXEC master.sys.sp_MSforeachdb @query
            
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

stpExtendedProperty_Database

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE PROCEDURE [dbo].[stpExtendedProperty_Database] (
    @Ds_Database sysname,
    @Ds_Texto VARCHAR(MAX)
)
AS BEGIN
 
 
    IF (LEN(LTRIM(RTRIM(@Ds_Texto))) = 0)
    BEGIN
        PRINT 'Texto não informado para a o database "' + @Ds_Database + '"'
        RETURN
    END
 
    
    DECLARE @query VARCHAR(MAX)
        
    SET @query = '
        
    IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.extended_properties A WHERE A.class = 0 AND A.name = ''MS_Description'') = 0)
    BEGIN
        
        IF ((SELECT COUNT(*) FROM [' + @Ds_Database + '].sys.databases WHERE name = ''' + @Ds_Database + ''') > 0)
        BEGIN
        
            EXEC [' + @Ds_Database + '].sys.sp_addextendedproperty
                @name = N''MS_Description'',
                @value = ''' + @Ds_Texto + '''
                    
        END
        ELSE
            PRINT ''O database "' + @Ds_Database + '" não existe para adicionar ExtendedProperty.''
                
    END
    ELSE BEGIN
            
        EXEC [' + @Ds_Database + '].sys.sp_updateextendedproperty
            @name = N''MS_Description'',
            @value = ''' + @Ds_Texto + '''
        
    END
    '
 
    
    BEGIN TRY
    
        EXEC(@query)
        
    END TRY
    
    BEGIN CATCH
        
        PRINT @query
        DECLARE @MsgErro VARCHAR(MAX) = 'Erro ao adicionar a ExtendedProperty: ' + ISNULL(ERROR_MESSAGE(), '')
        RAISERROR(@MsgErro, 10, 1) WITH NOWAIT
        RETURN
 
    END CATCH
    
END
GO

Can I document objects using an interface?

In addition to allowing you to document your database objects using line of code, you can also add Extended Property metadata using the SQL Server Management Studio (SSMS) interface and is quite simple, although it does not allow automation:

Extended Property Example in a Table

Extended Property Example in a Column

Example of a form for entering metadata and documenting the object in question

How to export already made documentation?

With the script below, you will be able to easily export all Extended Property metadata you have in the desired database. This is useful for scripting and applying to another instance of your environment without having to restore the database to it.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
SELECT
    'EXEC sys.sp_addextendedproperty @name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
WHERE
    class_desc = N'DATABASE';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.schemas B ON A.major_id = B.schema_id
WHERE
    A.class_desc = N'SCHEMA';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.tables A
    INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
    INNER JOIN sys.extended_properties C ON A.object_id = C.major_id
WHERE
    C.class = 1
    AND C.minor_id = 0
    AND
    (
        C.value <> '1'
        AND C.value <> 1
    );
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id
    INNER JOIN sys.tables C ON A.major_id = C.object_id
    INNER JOIN sys.schemas D ON C.schema_id = D.schema_id
WHERE
    A.class = 1
    AND
    (
        A.value <> '1'
        AND A.value <> 1
    );
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.tables A
    INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
    INNER JOIN sys.extended_properties C
    INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id
WHERE
    D.type_desc = N'PRIMARY_KEY_CONSTRAINT';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.tables A
    INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
    INNER JOIN sys.extended_properties C
    INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id
WHERE
    D.type_desc = N'UNIQUE_CONSTRAINT'
    AND
    (
        C.value <> '1'
        AND C.value <> 1
    );
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''TABLE'', @level1name = [' + D.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.check_constraints B ON A.major_id = B.object_id
    INNER JOIN sys.schemas C
    INNER JOIN sys.tables D ON C.schema_id = D.schema_id ON B.parent_object_id = D.object_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.indexes A
    INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id
    INNER JOIN sys.tables C
    INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id
WHERE
    B.class_desc = N'INDEX'
    AND A.is_primary_key = 0;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.foreign_keys B ON A.major_id = B.object_id
    INNER JOIN sys.tables C ON B.parent_object_id = C.object_id
    INNER JOIN sys.schemas D ON C.schema_id = D.schema_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(D.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(D.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.default_constraints A
    INNER JOIN sys.schemas B
    INNER JOIN sys.tables C ON B.schema_id = C.schema_id ON A.parent_object_id = C.object_id
    INNER JOIN sys.extended_properties D ON A.object_id = D.major_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''VIEW'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.views B ON A.major_id = B.object_id
    INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
    A.minor_id = 0;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id
    INNER JOIN sys.views C ON A.major_id = C.object_id
    INNER JOIN sys.schemas D ON C.schema_id = D.schema_id
WHERE
    A.class = 1
    AND
    (
        A.value <> '1'
        AND A.value <> 1
    );
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.indexes A
    INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id
    INNER JOIN sys.views C
    INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id
WHERE
    B.class_desc = N'INDEX';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''FUNCTION'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.objects B ON A.major_id = B.object_id
    INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
    B.type_desc LIKE N'%FUNCTION%'
    AND A.minor_id = 0;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''PROCEDURE'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.procedures B ON A.major_id = B.object_id
    INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
    A.minor_id = 0;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''TRIGGER'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.triggers B ON A.major_id = B.object_id
WHERE
    B.parent_class_desc = N'DATABASE';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.tables A
    INNER JOIN sys.triggers B ON A.object_id = B.parent_id
    INNER JOIN sys.extended_properties C ON B.object_id = C.major_id
    INNER JOIN sys.schemas D ON A.schema_id = D.schema_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.views A
    INNER JOIN sys.triggers B ON A.object_id = B.parent_id
    INNER JOIN sys.extended_properties C ON B.object_id = C.major_id
    INNER JOIN sys.schemas D ON A.schema_id = D.schema_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION FUNCTION'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.partition_functions B ON A.major_id = B.function_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION SCHEME'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.partition_schemes B ON A.major_id = B.function_id;
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.data_spaces B ON A.major_id = B.data_space_id
WHERE
    B.type_desc = 'ROWS_FILEGROUP';
 
 
SELECT
    'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + C.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + B.name + ' ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
    sys.extended_properties A
    INNER JOIN sys.database_files B ON A.major_id = B.file_id
    INNER JOIN sys.data_spaces C ON B.data_space_id = C.data_space_id
WHERE
    A.class_desc = N'DATABASE_FILE';

How can I view this information?

And now we come to the most interesting part of the formatting work, which is the visualization of the registered metadata. This view can be of 2 types:

  • Official documentation: Document in CHM, HTML, Word or PDF format, where you can view all information, such as tables, Stored Procedures, Views,
    etc, and their descriptions
  • During development: Tools that allow you to view documentation in the SSMS itself, allowing you to quickly access this information as you query and access objects.

Apex SQL Complete

Hardware free which aims to improve (and VERY) the productivity of SQL Server query writing in SSMS and is the main competitor of RedGate SQL Prompt.

One of the features of this tool is that it allows you to view Extended Property metadata of objects while you are programming, as shown below:

RedGate SQL Prompt

Business tool that aims to improve (and VERY) the productivity of writing SQL Server queries in SSMS and is the market leader (rightly) in this segment. It really is a sensational resource, in which I have even made the article SQL Server - Write T-SQL as a Ninja Using Redgate SQL Prompt.

One of the features of this tool is that it allows you to view Extended Property metadata of objects while you are programming, as shown below:

RedGate SQLDoc

Using this commercial RedGate documentation tool, you will be able to generate complete documentation of your databases, with statistical information, indexes, tables, procedures and more technical information about your instance objects and databases, as well as the metadata registered. for you (Extended Property).

Examples:

Dateedo

Using this commercial documentation tool (you can use a version of free, but with limitations), you will be able to generate a complete documentation of your databases, with statistics information, indexes, tables, procedures and a series of technical information about your instance objects and databases, as well as the metadata registered by you ( Extended Property).

Examples:

Want more options?

  • ApexSQL Doc (examples)
  • Dateedo
  • TMD Schema Reporter (examples)
  • dbdesc (examples)
  • DBScribe 1.4 for SQL Server (examples)
  • Document! X
  • DOCxPRESS
  • LiveDoco
  • Red-Gate's SQL Doc (demonstration)
  • SchemaToDoc (examples)
  • SQLDocKit
  • SqlSpec (examples)
  • SQL Data Dictionary
  • SQLDoc
  • SQL Documentation Tool (examples)
  • SQL Help Builder
  • T-SQL Source Code Unscrambler
  • turtle SQL

Conclusion

As you can see, there are several ways to document and describe your database objects. And there are several tools for generating official documentation and also for viewing this information in real time while you are programming.

To cite as an example a real case, in a given company, there was documentation of bank objects, but in Excel spreadsheets shared on the network. This spreadsheet was not up to date, the search for information was very bad, and most people had to constantly search the descriptions of each column in this spreadsheet, generating a huge waste of time from the BI and Development teams.

To improve the productivity of these teams, me and DBA Caroline goltara, we did a market analysis and opted for the Redgate SQL Doc tool as the company's official documentation tool.

We imported, in an automated way, all the texts registered in the spreadsheets into the database (using the SP's shown in this post) and the project was very successful, as this greatly accelerated the teams' productivity, since they used SQL Prompt there , loading the documentation metadata as soon as it was updated in the bank.

In addition, it was made available on a web server, the documentation generated in HTML format, updated and accessible to all.

I hope you enjoyed this post and that it can be helpful to you.
A big hug, see you later.

tags: documentationextended propertysqlsql server

You may also like ...

  • SQL Server - Power BI Gateway issue using external IP or hostname to access database

  • SQL Server - I changed Max Server Memory to 0 and now I can't connect to the instance

  • Working with Service Broker on Microsoft SQL Server

  • Next MCP Professional - How to Share Your Certifications and How to Find Certified Professionals
  • Previous SQL Server - How to Practically Share Your Execution Plans on the Web

Comments

  • Comments6
  • Pingbacks0
  1. Nissandro Ribeiro said:
    11 from 2018 from September to 11: 41

    YOU ARE THE MAN! THANK YOU, MY DEAR!

    Reply
    • Dirceu Resende said:
      18 from 2018 from September to 20: 42

      Thank you, Nissandro! Needing, we are at your orders!

      Reply
  2. Caroline goltara said:
    30 October 2017 to 14: 54

    Great post Dirceu!
    Really this form of documentation is very useful, very good to share this knowledge and success story.

    Reply
    • Dirceu Resende said:
      7 November 2017 to 21: 00

      Hey Carol! Good to see you here .. lol ..

      Many thanks for the feedback and help during the project that I mentioned in the post itself. It was thanks to you that he was a success story.

      Hug!

      Reply
  3. Meirieli said:
    29 October 2017 to 08: 38

    Excellent post, Dirceu! This is the kind of tip we hardly find in our research! Congratulations on the didactics and explanation of the subject! Go, go!

    Reply
    • Dirceu Resende said:
      29 October 2017 to 09: 52

      Thank you for your visit and feedback, Meirieli 🙂

      Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.