Hoje precisei mapear todos os objetos que referenciam uma determinada coluna de uma tabela, pois era necessário avaliar os impactos de uma possível exclusão deste campo. Essa tarefa pode ser facilitada pela Ferramenta de Visualização de Dependências de Objetos do SQL Sever Management Studio, que lista todos os objetos que referenciam o objeto escolhido. Porém, o grande número de stored procedures referenciadas ainda tornaria esta demanda muito demorada e trabalhosa.
Para agilizar esta tarefa, procurei automatizar o processo de busca consultando informações sobre os objetos nas tabelas de sistemas.
Ao criar uma Stored Procedure em um banco de dados SQL Server, as informações referentes à procedure são armazenadas em tabelas de sistema. Apesar de não ser possível alterar os registros armazenados nestas tabelas, o SQL Server permite que os usuários - com as devidas permissões - consultem seus dados através das Views de sistema.
Veja abaixo a idéia utilizada para buscar estas informações:
As view de sistema contém muitas informações relevantes para facilitar a vida de um DBA ou desenvolvedor SQL Server. Explore-as!
Até a próxima!
Para agilizar esta tarefa, procurei automatizar o processo de busca consultando informações sobre os objetos nas tabelas de sistemas.
Ao criar uma Stored Procedure em um banco de dados SQL Server, as informações referentes à procedure são armazenadas em tabelas de sistema. Apesar de não ser possível alterar os registros armazenados nestas tabelas, o SQL Server permite que os usuários - com as devidas permissões - consultem seus dados através das Views de sistema.
Veja abaixo a idéia utilizada para buscar estas informações:
SQL Server 2008
No SQL Server 2008, o script utilizado para criar a stored procedure fica armazenado no campo ROUTINE_DEFINITION da view de sistema INFORMATION_SCHEMA.ROUTINES. Assim, podemos procurar um texto no script de todas as procedures pertencentes ao banco através do script abaixo:
SELECT
ROUTINE_SCHEMA
, ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%texto desejado%'
ROUTINE_SCHEMA
, ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%texto desejado%'
SQL Server 2000
No SQL Server 2000, o script de criação da stored procedure fica armazenado no campo text da view de sistema syscomments. Assim, podemos procurar um texto no script de todas as procedures pertencentes ao banco através do script abaixo:
SELECT
O.name
FROM
syscomments C
INNER JOIN sysobjects O ON
C.id = O.id
WHERE
text like '%texto desejado%'
O.name
FROM
syscomments C
INNER JOIN sysobjects O ON
C.id = O.id
WHERE
text like '%texto desejado%'
As view de sistema contém muitas informações relevantes para facilitar a vida de um DBA ou desenvolvedor SQL Server. Explore-as!
Até a próxima!
11 comentários:
Isso ajuda muito na hora H, o problema é que existe diferença entre o 2k e o 2k8
Será que teria como encontrar uma coluna de uma tabela apartir de um texto
Olá Léo!
Para selecionar tabelas que contenham uma coluna com determinado texto, pode-se alterar o script acima para utilizar a view de sistema syscolumns.Veja como ficaria:
SELECT
O.name as Tabelas
FROM
syscolumns C
INNER JOIN sysobjects O ON
C.id = O.id
WHERE
c.name like '%Texto_Desejado%'
Espero ter ajudado.
Abraço!
Olá Evandro,
Saberia me dizer se há alguma stored procedure que recupera a query que foi digitada?
Olá!
Obrigado pelo seu contato!
A resposta para a sua pergunta vai depender do contexto em que você precisa recuperar esse comando.
Se for o último comando da sessão de banco que está aberta, é possível sim.
Bastaria executar, **EM OUTRA SESSÃO** o comando DBCC INPUTBUFFER (@spid). Onde o parâmetro @spid é o ID da sessão onde se quer capturar o último comando.
Obs1: Ressaltei que é necessário executar em outra sessão, pois em caso contrário o próprio comando DBCC INPUTBUFFER será retornado.
Obs2: Para obter o SPID de uma sessão de banco de dados, pode-se usar o comando SELECT @@spid.
Não sei se ficou claro. Se não ajudei, por favor forneça mais alguns detalhes.
Atenciosamente,
Evandro
Olá!
Ajudou bastante seu post. tenho também a necessidade de saber quais as procs cujos certos usuários tem acessos, mas são muitas procs pra eu olhar uma a uma. Tem alguma forma de fazer isso via query (SQL 2000)
Obrigado
Olá Marcos!
Não tenho disponível uma instância do SQL Server 2000 aqui para testar, mas acredito que a consulta abaixo deve ajudar:
SELECT
p.permission_name,
class_desc,
o.name
FROM
sys.database_permissions p
INNER JOIN sys.sysusers u on p.grantee_principal_id = u.uid
INNER JOIN sys.objects o p.major_id = o.object_id
WHERE
u.name = 'username'
Não me recordo se a stored procedure sp_helprotect está disponível na versão 2000, mas caso exista, ela também pode ajudar. Segue url com a referência:
http://technet.microsoft.com/pt-br/library/ms190310.aspx
Grande dica. Apenas um complemento: o routine_definition retorna apenas os 4000 primeiros bytes, então uma busca por conteúdo usando este campo pode não retornar o que se espera. para obter o código completo de qualquer entidade do banco, use a função OBJECT_DEFINITION em conjunto com o routine_definition. Exemplo:
SELECT
R.ROUTINE_SCHEMA
, R.ROUTINE_NAME ,
OBJECT_DEFINITION (OBJECT_ID(R.ROUTINE_NAME )) AS [codigo]
FROM
INFORMATION_SCHEMA.ROUTINES R
WHERE
OBJECT_DEFINITION (OBJECT_ID(R.ROUTINE_NAME )) LIKE '%texto a ser localizado%'
Preciso atualizar os Bancos de meus clientes com as procedures e trigers do meu banco que está atualizado. Como faço? O problema é que tenho umas 80 procs e tenho de salvar uma a uma em forma de script sql e depois rodar estes scripts um a um no cliente, isso em cada cliente, imagina se são muitos clientes e muitas procs...
Olá Luciano,
Eu sugiro que você utilize a ferramenta "Generate scripts" do Management Studio.
Com ela você pode selecionar os objetos de banco que deseja exportar, inclusive pode filtrar pelo tipo (Procedures, Tabelas, Views, etc).
No seu caso, você deve selecionar o tipo "Stored Procedures" e depois selecionar quais procs deseja gerar o script. Nas opções de geração, marque as opções "Script Drop" e "Script Create", pois dessa forma o script apagará todas as procedures existentes e criará novamente na versão atualizada. É possível gerar todas essas alterações em um único script, o que facilita a execução em vários ambientes.
Não se esqueça de adicionar as permissões (GRANTs) ao final do script, pois ao executar o DROP, os grants serão perdidos.
Obrigado pelo contato!
Postar um comentário