SQL Server – Como documentar o banco de dados e seus objetos (tabelas, procedures, colunas) utilizando Extended Property

Tempo de Leitura: 19 minutos

Olá pessoal!
Tudo bem com vocês ?

Neste post eu gostaria de comentar sobre algo muito importante durante o dia a dia de DBA’s e Desenvolvedores de Query, que é a documentação do banco de dados. Dificilmente eu vejo ambientes onde as colunas ou tabelas possuem uma descrição clara do que se trata esse objeto do banco.

Introdução

Para quem cria consultas o dia inteiro, como analistas de BI, essa informação facilita e muito, o entendimento das queries e a precisão das informações. Para inserir essas informações no banco de dados, vamos utilizar um recurso já bem antigo do SQL Server, mas que poucas pessoas utilizam ou até mesmo conhecem, que é o Extended Property.

Utilizando procedures de sistemas, podemos descrever objetos de banco de dados utilizando suas próprias palavras para facilitar o entendimento de outras pessoas.

Uma vez que essas descrições são inseridas no banco de dados, você pode utilizar ferramentas para visualizar essa informação enquanto você desenvolve suas consultas ou mesmo para gerar documentações completas do banco de dados a partir das descrições do Extended Property.

Diferente de algumas ferramentas de documentação de banco de dados, esse recurso guarda as descrições no próprio banco de dados, fazendo com que o tempo gasto para cadastrar essas descrições não seja perdido caso você pretenda trocar a ferramenta de documentação, fora que essas descrições ficarão salvas em um local seguro e com backups (seu banco tem backup né?!).

Como documentar bases de dados SQL Server

Para a documentação das bases de dados SQL Server, vamos utilizar o recurso chamado Extended Property e as procedures de sistema sp_addextendedproperty, sp_updateextendedproperty e sp_dropextendedproperty.

Para que se possa utilizar essas procedures, o usuário deve estar nas database roles db_owner ou ddl_admin (essa role não permite adicionar descrições para o próprio banco de dados, usuários ou roles) ou ter o privilégio de ALTER/CONTROL nos objetos que ele deseja adicionar as descrições. E é claro, usuários de server roles, como sysadmin, também podem utilizar essas procedures.

Os tipos de objetos que podem ser documentados utilizando Extended Property utilizando essas SP’s de sistema (@level1type) são: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW e XML SCHEMA COLLECTION.

Os subtipos de objetos que podem ser documentados (@level2type) são: COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER e TRIGGER. Os subtipos (@level2type) dependem do tipo (@level1type) para defini-los, como por exemplo, para documentar uma coluna, onde você deverá definir o @level1type = ‘TABLE’ e @level2type = ‘COLUMN’, referenciando a qual tabela essa coluna faz parte.

Visando facilitar a utilização dessas procedures, vou disponibilizar aqui algumas procedures que vão verificar se o objeto em questão já possui Extended Property e, caso tenha, utiliza a sp_updateextendedproperty ou caso não tenha, utiliza a sp_addextendedproperty.

stpExtendedProperty_Tabela

Visualizar código-fonte

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

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

stpExtendedProperty_Coluna

Visualizar código-fonte

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

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

stpExtendedProperty_Trigger

Visualizar código-fonte

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

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

stpExtendedProperty_View

Visualizar código-fonte

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

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 = '

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

stpExtendedProperty_Procedure

Visualizar código-fonte

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

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 = '

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

stpExtendedProperty_Function

Visualizar código-fonte

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

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 = '

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

stpExtendedProperty_Usuario

Visualizar código-fonte

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

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

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

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

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

stpExtendedProperty_Database

Visualizar código-fonte

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

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

Posso documentar os objetos utilizando uma interface?

Além de permitir que você possa documentar seus objetos de bancos de dados utilizando linha de código, você também pode adicionar metadados Extended Property utilizando a interface do SSMS (SQL Server Management Studio) e é bem simples, embora não permita automatização:

Exemplo de Extended Property numa Tabela

Exemplo de Extended Property numa coluna

Exemplo de um formulário para digitar os metadados e documentar o objeto em questão

Como exportar as documentações já feitas?

Com o script abaixo, você poderá gerar facilmente exportar todas os metadados de Extended Property que você possui no database desejado. Isso é útil para gerar um script e aplicar em outra instância do seu ambiente sem precisar fazer o restore do database para isso.

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';

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

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';

Como posso visualizar essas informações?

E agora chegamos na parte mais interessante do trabalho de formatação, que é a visualização dos metadados cadastrados. Essa visualização pode ser de 2 tipos:

Documentação oficial: Documento no formato CHM, HTML, Word ou PDF, onde pode-se visualizar todas as informações, como tabelas, Stored Procedures, Views,
etc, e suas descrições
Durante o desenvolvimento: Ferramentas que possibilitam a visualização da documentação no próprio SSMS, permitindo que você acesse essas informações rapidamente, conforme você vai consultando e acessando os objetos.

Apex SQL Complete

Ferramenta gratuita que tem por objetivo, melhorar (e MUITO) a produtividade na escrita de consultas do SQL Server no SSMS e é o principal concorrente do RedGate SQL Prompt.

Um dos recursos dessa ferramenta, é permitir que você possa visualizar metadados (Extended Property) de objetos enquanto você está programando, conforme demonstrações abaixo:

RedGate SQL Prompt

Ferramenta comercial que tem por objetivo, melhorar (e MUITO) a produtividade na escrita de consultas do SQL Server no SSMS e é líder de mercado (com razão) nesse segmento. Realmente é um recurso sensacional, no qual já até fiz o artigo SQL Server – Escreva T-SQL como um Ninja utilizando o Redgate SQL Prompt.

Um dos recursos dessa ferramenta, é permitir que você possa visualizar metadados (Extended Property) de objetos enquanto você está programando, conforme demonstrações abaixo:

RedGate SQLDoc

Utilizando essa ferramenta comercial de documentação da RedGate, você poderá gerar uma documentação completa das suas bases de dados, com informações de estatísticas, índices, tabelas, procedures e mais uma série de informações técnicas sobre seus objetos e databases da instância, além dos metadados cadastrados por você (Extended Property).

Exemplos:

Dataedo

Utilizando essa ferramenta comercial de documentação (você pode usar uma versão gratuita, mas com limitações), você poderá gerar uma documentação completa das suas bases de dados, com informações de estatísticas, índices, tabelas, procedures e mais uma série de informações técnicas sobre seus objetos e databases da instância, além dos metadados cadastrados por você (Extended Property).

Exemplos:

Quer mais opções?

Conclusão

Como vocês puderam observar, existem diversas formas de documentar e descrever seus objetos de banco de dados. E existem várias ferramentas para gerar documentações oficiais e também para visualizar essas informações em tempo real, enquanto você vai programando.

Citando como exemplo um caso real, em uma determinada empresa, existia uma documentação dos objetos de banco, mas em planilhas do Excel compartilhadas na rede. Essa planilha não era atualizada, a busca das informações era muito ruim e a maioria das pessoas tinha que ficar constantemente pesquisando as descrições de cada coluna nessa planilha, gerando um desperdício de tempo muito grande das equipes de BI e Desenvolvimento.

Para melhorar a produtividade dessas equipes, eu e a DBA Caroline Goltara, fizemos uma análise de mercado e optamos pela ferramenta Redgate SQL Doc como ferramenta oficial de documentação da empresa.

Importamos, de forma automatizada, todos os textos cadastrados nas planilhas para o banco de dados (utilizando as SP’s demonstradas nesse post) e o projeto teve um êxito muito grande, pois isso agilizou muito a produtividade das equipes, já que utilizavam o SQL Prompt lá, carregando os metadados das documentações assim que a mesma era atualizado no banco.

Além disso, foi disponibilizada num servidor Web, a documentação gerada no formato HTML, atualizada e acessível a todos.

Espero que tenham gostado desse post e que ele possa ser útil para vocês.
Um abraço e até mais.

Nissandro Ribeiro disse:
11 de setembro de 2018 às 11:41
VOCÊ É O CARA! OBRIGADO MEU QUERIDO!
Responder
- Dirceu Resende disse:
  18 de setembro de 2018 às 20:42
  Obrigado, Nissandro! Precisando, estamos às ordens!
  Responder
Caroline Goltara disse:
30 de outubro de 2017 às 14:54
Ótimo post Dirceu!
Realmente esta forma de documentação é muito útil, muito bom compartilhar este conhecimento e case de sucesso.
Responder
- Dirceu Resende disse:
  7 de novembro de 2017 às 21:00
  Ei Carol! Que bom ver você por aqui.. rs..
  Muito obrigado pelo feedback e pela ajuda durante o projeto que citei no próprio post. Foi graças a você que ele foi um caso de sucesso.
  Abraço!
  Responder
Meirieli disse:
29 de outubro de 2017 às 08:38
Excelente post, Dirceu! Esse é o tipo de dica que dificilmente achamos em nossas pesquisas! Parabéns pela didática e explanação do assunto! Go, Go!
Responder
- Dirceu Resende disse:
  29 de outubro de 2017 às 09:52
  Obrigado pela visita e pelo feedback, Meirieli 🙂
  Responder

Other Languages

Assinar blog por e-mail

Visualizações do Blog

Categorias

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

SQL Server – Como documentar o banco de dados e seus objetos (tabelas, procedures, colunas) utilizando Extended Property

Introdução

Como documentar bases de dados SQL Server

stpExtendedProperty_Tabela

stpExtendedProperty_Coluna

stpExtendedProperty_Trigger

stpExtendedProperty_View

stpExtendedProperty_Procedure

stpExtendedProperty_Function

stpExtendedProperty_Usuario

stpExtendedProperty_Database

Posso documentar os objetos utilizando uma interface?

Como exportar as documentações já feitas?

Como posso visualizar essas informações?

Apex SQL Complete

RedGate SQL Prompt

RedGate SQLDoc

Dataedo

Quer mais opções?

Conclusão

You may also like...

6 Responses

Deixe uma resposta Cancelar resposta

Introdução

Como documentar bases de dados SQL Server

stpExtendedProperty_Tabela

stpExtendedProperty_Coluna

stpExtendedProperty_Trigger

stpExtendedProperty_View

stpExtendedProperty_Procedure

stpExtendedProperty_Function

stpExtendedProperty_Usuario

stpExtendedProperty_Database

Posso documentar os objetos utilizando uma interface?

Como exportar as documentações já feitas?

Como posso visualizar essas informações?

Apex SQL Complete

RedGate SQL Prompt

RedGate SQLDoc

Dataedo

Quer mais opções?

Conclusão

You may also like...

Free ebooks sobre SQL Server para Download

[Vídeo] – Introdução ao SQLCLR

SQL Server – Como ativar/habilitar o usuário sa

6 Responses

Deixe uma resposta Cancelar resposta