Dicionário de Dados – A Exclusão. – Logan Destefani Merazzi

Um dos meus papéis dentro da empresa onde trabalho é o de ~~ser chato e~~ verificar os scripts que são enviados antes de entrar em produção.

Uma parte das verificações é garantir que o dicionário de dados esteja o mais atualizado possível para que consigamos entender o objetivos das tabelas, colunas, constraints, etc.

Para inserir uma propriedade em um objeto, deve ser utilizada a procedure sp_addextendedproperty. Para remover: sp_dropextendedproperty. E para atualizar: sp_updateextendedproperty. Não vou entrar no mérito de como usar, pois o BOL (Books On Line – RTFM!) explica bem como usar e os parâmetros necessários.

Há um bom tempo atrás (2011 O.o) escrevi um post sobre como ver as descrições através do SSMS, mas uma questão que surgiu agora foi “E se eu precisar remover essas propriedades de uma vez – Seja qual for o motivo?”

Infelizmente, não tem uma mágica (eu pelo menos desconheço), pois a propriedade é por objeto, então não rola um ‘exec sp_dropextendedproperty EVERYTHING’ (apesar de não ser uma má ideia de implementação ;))

A (minha) solução: montar um select que monte o exec para você. Aí, como você vai executar ele, se num script via CTRL+C/CTRL+V~~, se via cursor~~… Cabe à sua imaginação…

O script fica assim:

/*
  Criado por: Logan Destefani Merazzi
  Script que realiza a exclusão do dicionário de dados das bases clientes.
  São excluídas as propriedades de: 
   - Tabelas
   - Colunas
   - Indices
   - Constraints (Check, FK, UK, PK)
  Demais objetos (procedures, functions, views, etc), fica como lição de casa. ;)
*/

BEGIN TRANSACTION RemoveDicionario

BEGIN TRY

  SET NOCOUNT ON

  DECLARE @psScript varchar(max)
  SET  @psScript = ''

  --Tabela
  SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '
  '
  FROM sys.extended_properties sep
  JOIN sys.tables tab
  ON 
    sep.major_id = tab.object_id
  WHERE 
    sep.class_desc = 'OBJECT_OR_COLUMN' AND
    sep.minor_id = 0 AND
    SEP.NAME = 'MS_Description' -- Para não retornar os comentários dos diagramas.

  --colunas
  SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''
  ,@level2type = ''column''
  ,@level2name = ''' + col.name + '''' + '
  '
  FROM sys.extended_properties AS sep
  JOIN sys.columns AS col
  ON 
    col.object_id = sep.major_id AND
    col.column_id = sep.minor_id
  WHERE
    sep.class_desc = 'OBJECT_OR_COLUMN' AND
    sep.minor_id > 0 AND
    SEP.NAME = 'MS_Description'

  -- Indexes
  SELECT @psScript = cast(@psScript as varchar(max)) +  'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''
  ,@level2type = ''index''
  ,@level2name = ''' + sys.indexes.name + '''' + '
  '
  FROM sys.extended_properties AS sep 
  JOIN sys.indexes 
  ON 
    sys.indexes.object_id = sep.major_id AND 
    sys.indexes.index_id = sep.minor_id
  WHERE
    (sep.class_desc = 'INDEX') AND
    (sep.minor_id > 0) AND 
    SEP.NAME = 'MS_Description'

  --check constraints
  SELECT @psScript = cast(@psScript as varchar(max)) +   'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''
  ,@level2type = ''constraint''
  ,@level2name = ''' + cc.name + ''''+ '
  '
  FROM sys.extended_properties sep
  JOIN sys.check_constraints cc 
  ON
    sep.major_id = cc.object_id AND 
    SEP.NAME = 'MS_Description'

  -- UKs
  SELECT @psScript = cast(@psScript as varchar(max)) +   'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(kc.parent_object_id) + '''
  ,@level2type = ''constraint''
  ,@level2name = ''' + kc.name + '''' + '
  '
  FROM sys.extended_properties sep
  JOIN sys.key_constraints kc 
  ON
    sep.major_id = kc.object_id AND 
    SEP.NAME = 'MS_Description' AND
    kc.type_desc = 'UNIQUE_CONSTRAINT'

  --FKs
  SELECT @psScript = cast(@psScript as varchar(max)) +   'EXEC sys.sp_dropextendedproperty
  @name = ''' + sep.name + '''
  ,@level0type = ''schema''
  ,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''
  ,@level1type = ''table''
  ,@level1name = ''' + OBJECT_NAME(fk.parent_object_id) + '''
  ,@level2type = ''constraint''
  ,@level2name = ''' + fk.name + '''' + '
  '
  FROM sys.extended_properties sep
  JOIN sys.foreign_keys fk 
  ON
    sep.major_id = fk.object_id AND 
    SEP.NAME = 'MS_Description'

  --PKs
  SELECT @psScript = cast(@psScript as varchar(max)) +   'EXEC sys.sp_dropextendedproperty
  @level0type = N''SCHEMA'', @level0name = ['+ SCH.name + '], 
  @level1type = ''TABLE'', @level1name = [' + Tab.name + '] , 
  @level2type = ''CONSTRAINT'', @level2name = [' + KC.name + '] ,
  @name = ''' + SEP.name + ''''
   + '
  '
  FROM sys.tables Tab
  JOIN sys.schemas SCH 
  ON 
    Tab.schema_id = SCH.schema_id
  JOIN sys.key_constraints KC 
  ON 
    Tab.object_id = KC.parent_object_id
  JOIN sys.extended_properties SEP
  ON 
    SEP.major_id = KC.object_id 
  WHERE
    KC.type_desc = N'PRIMARY_KEY_CONSTRAINT'  AND 
    SEP.NAME = 'MS_Description'

  --select @psScript
  exec (@psScript)

  -- Terminou sem erros...
  commit tran RemoveDicionario

END TRY
BEGIN CATCH

  IF @@TRANCOUNT > 0
    rollback tran RemoveDicionario

  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

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

Criado por: Logan Destefani Merazzi

Script que realiza a exclusão do dicionário de dados das bases clientes.

São excluídas as propriedades de:

- Tabelas

- Colunas

- Indices

- Constraints (Check, FK, UK, PK)

Demais objetos (procedures, functions, views, etc), fica como lição de casa. ;)

BEGIN TRANSACTION RemoveDicionario

BEGIN TRY

SET NOCOUNT ON

DECLARE @psScript varchar(max)

SET @psScript = ''

--Tabela

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

FROM sys.extended_properties sep

JOIN sys.tables tab

sep.major_id = tab.object_id

WHERE

sep.class_desc = 'OBJECT_OR_COLUMN' AND

sep.minor_id = 0 AND

SEP.NAME = 'MS_Description' -- Para não retornar os comentários dos diagramas.

--colunas

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''

,@level2type = ''column''

,@level2name = ''' + col.name + '''' + '

FROM sys.extended_properties AS sep

JOIN sys.columns AS col

col.object_id = sep.major_id AND

col.column_id = sep.minor_id

WHERE

sep.class_desc = 'OBJECT_OR_COLUMN' AND

sep.minor_id > 0 AND

SEP.NAME = 'MS_Description'

-- Indexes

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''

,@level2type = ''index''

,@level2name = ''' + sys.indexes.name + '''' + '

FROM sys.extended_properties AS sep

JOIN sys.indexes

sys.indexes.object_id = sep.major_id AND

sys.indexes.index_id = sep.minor_id

WHERE

(sep.class_desc = 'INDEX') AND

(sep.minor_id > 0) AND

SEP.NAME = 'MS_Description'

--check constraints

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + ''''+ '

FROM sys.extended_properties sep

JOIN sys.check_constraints cc

sep.major_id = cc.object_id AND

SEP.NAME = 'MS_Description'

-- UKs

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(kc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + kc.name + '''' + '

FROM sys.extended_properties sep

JOIN sys.key_constraints kc

sep.major_id = kc.object_id AND

SEP.NAME = 'MS_Description' AND

kc.type_desc = 'UNIQUE_CONSTRAINT'

--FKs

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(fk.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + fk.name + '''' + '

FROM sys.extended_properties sep

JOIN sys.foreign_keys fk

sep.major_id = fk.object_id AND

SEP.NAME = 'MS_Description'

--PKs

SELECT @psScript = cast(@psScript as varchar(max)) + 'EXEC sys.sp_dropextendedproperty

@level0type = N''SCHEMA'', @level0name = ['+ SCH.name + '],

@level1type = ''TABLE'', @level1name = [' + Tab.name + '] ,

@level2type = ''CONSTRAINT'', @level2name = [' + KC.name + '] ,

@name = ''' + SEP.name + ''''

+ '

FROM sys.tables Tab

JOIN sys.schemas SCH

Tab.schema_id = SCH.schema_id

JOIN sys.key_constraints KC

Tab.object_id = KC.parent_object_id

JOIN sys.extended_properties SEP

SEP.major_id = KC.object_id

WHERE

KC.type_desc = N'PRIMARY_KEY_CONSTRAINT' AND

SEP.NAME = 'MS_Description'

--select @psScript

exec (@psScript)

-- Terminou sem erros...

commit tran RemoveDicionario

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

rollback tran RemoveDicionario

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

Como está no comentário, fica como lição de casa a exclusão dos demais objetos…

O script pode ser baixado aqui, no meu git.

[]’s!

Dicionário de Dados – A Exclusão.

Curtir isso:

Relacionado

Deixe uma respostaCancelar resposta

Compartilhe isso:

Curtir isso:

Relacionado

Deixe uma respostaCancelar resposta