sp_rename - ¿Cómo encontrar el nombre del procedimiento almacenado, basado en la búsqueda del nombre de la tabla, utilizando SQL Server 2008?

stored procedure sql server ejemplos

Question

Quiero encontrar todos los procedimientos almacenados donde se utiliza una tabla en particular. Hay muchos procedimientos almacenados en la base de datos, por lo que no es posible verificar cada procedimiento.

¿Hay alguna forma de utilizar una consulta de búsqueda para que pueda encontrar los procedimientos almacenados?

He intentado este código:

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ''% RejectionReason %''

Donde RejectionReason es el nombre de mi tabla, pero muestra todos los procedimientos donde RejectionReason se usa como nombre de columna, por lo que no funciona.

Answer 1

Aquí hay un trozo de código para que funcione. Solo cambia el nombre de la tabla, depende de tu código.

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE ''%tablename%''

p.ej:

SELECT DISTINCT so.name 
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id 
WHERE sc.text LIKE ''%users%''

Obtendrá la lista de procedimientos de la tienda y las relaciones de la tabla.

Answer 2

Esto devolverá SP''s y Vistas.

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.object_id=o.object_id
WHERE m.definition Like ''%TableName%''

Answer 3

Estoy utilizando el siguiente script SQL para buscar nombres de columna y texto dentro de todos los procedimientos almacenados de su base de datos . También puede utilizarlo para buscar tablas en procedimientos almacenados.

Especifique el término de búsqueda en la variable @SearchFor (como lo usaría en una expresión LIKE , por ejemplo, ''%LastName%'' para encontrar columnas y procedimientos almacenados que contengan LastName ).

Encontrará los nombres de las columnas en las tablas, así como el texto dentro de los procedimientos almacenados. La secuencia de comandos incluso puede mostrar el código fuente de SP, si establece @SPNameOnly en 0.

--
-- Purpose: Search field names in all tables, views stored procedures
--

DECLARE @SearchFor nvarchar(max)=''%Search_SP_Or_Table_Or_View%'' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code

-- tables
if (@SearchSP=1) begin  
  (
  select ''[''+c.table_Schema+''].[''+c.table_Name+''].[''+c.column_name+'']'' [schema_object], 
            t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  UNION
  select ''[''+routine_Schema+''].[''+routine_Name+'']'' [schema_object], 
         ''PROCEDURE'' as table_type from information_schema.routines
  where routine_definition like @SearchFor or routine_name like @SearchFor 
        and routine_type=''procedure''
  )
  order by table_type, schema_object
end else begin
  select ''[''+c.table_Schema+''].[''+c.table_Name+''].[''+c.column_name+'']'' [schema_object], 
         t.table_type 
  from information_schema.columns c
  left join information_schema.Tables t on c.table_name=t.table_name
  where column_name like @SearchFor or t.table_name like @SearchFor 
  order by c.table_Name, c.column_name
end     
-- stored procedure (source listing)
if (@SearchSP=1) begin      
    if (@DisplaySPSource=1) begin
      select ''[''+routine_Schema+''].[''+routine_Name+'']'' [schema.sp], routine_definition 
      from information_schema.routines
      where routine_definition like @SearchFor  or routine_name like @SearchFor 
      and routine_type=''procedure''
      order by routine_name
    end
end

Answer 4

Hay dos posibilidades que conozco.

En primer lugar SQL Management Studio tiene una opción para mostrar Dependencias. Haga clic con el botón derecho en la tabla y seleccione View Dependencies Sin embargo, esto no resaltará las usps donde el nombre de la tabla está incrustado en SQL dinámico.

La segunda opción es hacer clic derecho en la base de datos y seleccionar Generate Scripts . Siga el asistente y guarde todos los usps a una nueva ventana de consulta, luego busque el nombre de su tabla. Esto es más laborioso, pero encontrará todos los usos.

Answer 5

Según MSDN sp_depends se eliminará en futuras versiones en caso de que esté usando eso, puede usar la siguiente query lugar:

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities (''dbo.TableName'', ''OBJECT'');

Answer 6

Supongo que esta secuencia de comandos muestra todos los objetos dependientes de la tabla, incluidos los SP.

USE MYDatabase
GO

DECLARE @TableName varchar(100)
SET @TableName = ''mytable''

SELECT
 SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)
 ,SourceObject                 = OBJECT_NAME(sed.referencing_id)
 ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())
 ,ReferencedSchema             = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
 ,ReferencedObject             = sre.referenced_entity_name
 ,ReferencedColumnID   = sre.referenced_minor_id
 ,ReferencedColumn             = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ ''.'' + OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName

Para más detalles puedes echar un vistazo. http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/

Answer 7

SysObjects almacena información básica sobre todos los objetos dentro de su base de datos. Es útil que lo sepa porque nos dice el nombre de cada objeto y el tipo de objeto.

SysComments almacena el texto real (código) para sus procedimientos y funciones almacenados. Contiene un campo de ID que se asigna de nuevo al campo de ID en SysObjects.

select so.name, text
from sysobjects so, syscomments sc
where so.id = sc.id
and text like ''%RejectionReason%''

Answer 8

Trate de usar la herramienta de búsqueda gratuita de RedGate SQL .

Answer 9

SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
    ON d.object_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_major_id = p.object_id
    AND o.name = ''RejectionReason''

o

SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE ''%RejectionReason%'' 

o

EXEC sp_depends @objname = N''RejectionReason'';

Si ninguno de estos le ayuda a consultar este blog: http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/