ejemplos - Encuentre todas las referencias a un objeto en una base de datos de SQL Server

replicate sql ejemplos

Question

Estoy tratando de encontrar todas las referencias a un objeto en una base de datos de SQL Server.

¿Cómo puedo buscar rápidamente? SQL Server Management Studio no parece hacerlo. Utilizo http://www.red-gate.com/products/SQL_Search/ pero me gustaría encontrar la solución "oficial" de Microsoft para esto. ¿Está en otro producto?

Por ejemplo, cuando hago una búsqueda masiva en Visual Studio, me gustaría poder encontrar algo en todos los procedimientos almacenados.

O tal vez no estoy codificando esto de la manera correcta?

Carl

Answer 1

Con el uso de un SP de SQL no documentado: sp_msforeachdb

exec sp_msforeachdb ''
USE [?];
--IF DB_NAME() NOT IN (''''master'''',''''tempdb'''',''''model'''',''''msdb'''')
BEGIN
DECLARE
@SearchStr varchar(100)
SET @SearchStr = ''''%column_store_segments%'''';
SELECT DISTINCT
''''?'''' as db_name, o.name
, ( CASE upper(o.xtype)
WHEN ''''C'''' THEN ''''CHECK constraint''''
WHEN ''''D'''' THEN ''''Default or DEFAULT constraint''''
WHEN ''''F'''' THEN ''''FOREIGN KEY constraint''''
WHEN ''''L'''' THEN ''''Log''''
WHEN ''''FN'''' THEN ''''Scalar function''''
WHEN ''''IF'''' THEN ''''Inline table-function''''
WHEN ''''PK'''' THEN ''''PRIMARY KEY or UNIQUE constraint''''
WHEN ''''P'''' THEN ''''Stored procedure''''
WHEN ''''R'''' THEN ''''Rule''''
WHEN ''''RF'''' THEN ''''Replication filter stored procedure''''
WHEN ''''S'''' THEN ''''System table''''
WHEN ''''TF'''' THEN ''''Table function''''
WHEN ''''TR'''' THEN ''''Trigger''''
WHEN ''''U'''' THEN ''''User table''''
WHEN ''''V'''' THEN ''''View''''
WHEN ''''UQ'''' THEN ''''UNIQUE constraint (type is K)''''
WHEN ''''X'''' THEN ''''Extended stored procedure''''
ELSE upper(o.xtype) END ) Type
, ( CASE upper(o.xtype)
WHEN ''''PK'''' THEN ( select object_name(parent_object_id) FROM sys.key_constraints (nolock) WHERE o.name=name )
WHEN ''''F'''' THEN ( select object_name(parent_object_id) FROM sys.foreign_keys (nolock) WHERE o.name=name )
WHEN ''''TR'''' THEN ( select object_name(parent_id) FROM sys.triggers (nolock) WHERE o.name=name )
ELSE '''''''' END ) as Parent_Object
FROM sysobjects o (nolock)
INNER JOIN syscomments sc (nolock) ON o.id = sc.id
WHERE UPPER( text ) LIKE UPPER( @SearchStr ) AND substring(o.name,1,3)<> ''''dt_''''
GROUP BY o.name, o.xtype
END''
GO

Answer 2

En SQL 2008 se agregó la DMV (función de administración de datos) sys.dm_sql_referencing_entities. Devuelve cualquier objeto que haga referencia al objeto que se le pasa.

SELECT * FROM sys.dm_sql_referencing_entities(''dbo.Table1'', ''OBJECT'')

Answer 3

En SQL Server 2000, aquí hay una consulta que puede buscar dentro de definiciones de objetos, admitiendo cadenas de búsqueda de hasta 2000 caracteres. Utiliza los trozos en la tabla de syscomments .

SELECT O.name, O.xtype
FROM sysobjects O
WHERE EXISTS (
SELECT *
FROM
(
SELECT
Chunk = Substring(C1.text, T.Offset, 4000)
+ Coalesce(Substring(C2.text, 1, T.AdditionalLength), '''')
FROM
syscomments C1
CROSS JOIN (
SELECT 1, 0
UNION ALL
SELECT 2001, 2000
) T (Offset, AdditionalLength)
LEFT JOIN syscomments C2
ON C1.id = C2.id
AND C1.colid + 1 = C2.colid
AND T.Offset > 1
WHERE
O.id = C1.id
) C
WHERE
Chunk LIKE ''%search string%''
);

Answer 4

Encontré una solución como esta ..

USE [Database]
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = ''SP_Pay_GetData''
order by referencing_object_name

http://blog.sqlauthority.com/2012/12/02/sql-server-find-referenced-or-referencing-object-in-sql-server-using-sys-sql_expression_dependencies/

Answer 5

No estoy seguro de la manera ''oficial de microsoft'', pero he usado SqlDigger en el pasado. No está mal.

Si desea hacerlo en VS, necesitará el texto de todos sus procedimientos incluidos en su proyecto.

Answer 6

SQL Server Management Studio tiene una función de Dependencias de vista cuando hace clic derecho en un objeto en el Explorador de objetos. ¿Es esto lo que estás buscando?

Answer 7

Si desea utilizar OMG Ponies sql como método abreviado de teclado en SSMS, agregue el siguiente SP a su base de datos principal.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FindAllReferences]
@targetText nvarchar(128)
AS
BEGIN
SET NOCOUNT ON;
declare @origdb nvarchar(128)
select @origdb = db_name()
declare @sql nvarchar(1000)
set @sql = ''USE ['' + @origdb +''];''
set @sql += ''select object_name(m.object_id), m.* ''
set @sql += ''from sys.sql_modules m where m.definition like N'' + CHAR(39) + ''%'' + @targetText + ''%'' + CHAR(39)
exec (@sql)
SET NOCOUNT OFF;
END

Luego, solo necesita agregar dbo.SP_FindAllReferences a los atajos de teclado y luego puede usarlo en el contexto de cualquier base de datos en su servidor.

¡Aclamaciones!

NB: si está utilizando SQL Server 2005, tendrá que reemplazar

@sql +=

con

@sql = @sql +

Answer 8

Utilizar:

select object_name(m.object_id), m.*
from sys.sql_modules m
where m.definition like N''%name_of_object%''

... porque SYSCOMMENTS y INFORMATION_SCHEMA.routines tienen columnas nvarchar (4000). Por lo tanto, si se usa "name_of_object" en la posición 3998, no se encontrará. SYSCOMMENTS tiene varias líneas, pero INFORMATION_SCHEMA.routines trunca.

Answer 9

Utilizo esta consulta para buscar todas las tablas (o texto) en los procedimientos almacenados:

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ''%tablename%''