Processar os procedimentos armazenados numa base de dados

Question

Processar os procedimentos armazenados numa base de dados

pediram-me para encontrar o impacto da alteração de um nome de campo numa tabela e, subsequentemente, encontrar o impacto desta alteração.

Embora seja muito fácil identificar as tabelas afetadas, tenho um problema em tentar identificar os procedimentos de loja afetados.

Espero também que este guião ajude também ao desenvolvimento e seja capaz de identificar todos os procedimentos necessários.

Tenho um código abaixo que cria uma tabela temporária com um nome de procedimento e um campo que irá conter o texto do procedimento (sp_helptext). Meu plano (embora não elegante) pode obter o nome do procedimento, paramaters e texto do procedimento se eu puder obter o conteúdo do sp_helptext no campo.

--drop procedure dbo.sp_getProcText
create procedure dbo.sp_getProcText
(
      @proc_name varchar(max),
      @output varchar(max) output
)  

AS  
    BEGIN  
        declare @exec_string varchar(max);
        set @exec_string = 'sp_helptext' + ' ' +  @proc_name 
        exec sp_executesql @exec_string, @output
    END
go

create table #temp_sp_parse( proc_name varchar(max), proc_text varchar(max));
go

declare @proc_text_out varchar(max)

insert into 
    #temp_sp_parse (proc_name, proc_text)
        select 
            sysobjects.name , exec dbo.sp_getProcText(sysobjects.name, @proc_text_out) 
        from 
            dbo.sysobjects 
        join 
            dbo.syscolumns  on 
                syscolumns.id = sysobjects.id 
        where 
        (
            syscolumns.name like N'%rti%' and
            sysobjects.name like '%%'
        )
        and 
            sysobjects.type ='P'
go

select * from #temp_sp_parse
drop procedure sp_getProcText
drop table #temp_sp_parse

o MS SQL tem um problema com o exec dentro da sequência inserida.

Alguém pode recomendar uma solução ou, possivelmente, uma solução melhor?

0

sql-server sql parsing procedure

Author: marc_s, 2015-10-15

Source

1 answers

score 0 · Accepted Answer

Escrevi um post no blog sobre isso.: http://anehir.blogspot.com.tr/2012/11/ms-sql-search-within-programmable.html

Basicamente você usa uma vista de sistema: sys.syscomments.
O código completo está abaixo:

declare @keyword1 varchar(50) = 'SomeTable'
declare @keyword2 varchar(50) = 'SomeColumn'

create table #t
(
 [ReferencingDatabase] varchar(100),
 [ReferencingSchema] varchar(100),
 [ReferencingObject] varchar(100),
 [ReferencingObjectType] varchar(100),
 [HelpText] varchar(300)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
use ?
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
 insert into #t
 select
  ''?'' as [ReferencingDatabase],
  s.name as [ReferencingSchema],
  o.name as [ReferencingObject],
  o.type_desc as [ReferencingObjectType],
  ''sp_helptext '''''' + s.Name + ''.'' + o.name + '''''''' as [Script]
 from sys.syscomments c
 left outer join sys.objects o on o.object_id = c.id
 left outer join sys.schemas s on s.schema_id = o.schema_id
 where
 c.text like ''%' + @keyword1 + '%''
 and c.text like ''%' + @keyword2 + '%''
end
'
exec sp_msforeachdb @cmd1
select * from #t
drop table #t