Início / dba / Perguntas / 154284
Accepted
Matthew Verstraete
Asked: 2016-11-05 07:11:09 +0800 CST

Encontrar todas as funções/procedimentos armazenados que fazem referência a um determinado procedimento/função?

  • 772

Recebi a tarefa de mover relatórios do SSRS para nosso aplicativo ASP.Net interno e isso me envolveu reescrever algumas funções e um procedimento armazenado para melhorar o desempenho, torná-los saída em formulários melhor consumidos pelo aplicativo e tornar quaisquer adições solicitadas aos relatórios.

O que eu também preciso fazer é remover as funções e procedimentos antigos do sistema à medida que vou, antes de fazer isso, embora eu queira ter certeza de que não quebrará mais nada. Existe uma maneira que eu possa passar por todas as precedências e funções para ver se alguma delas contém referências às que estarei removendo?

Existem milhares deles para ir embora e eu preferiria não ter que abrir cada um de cada vez no SSMS para olhar. Então, se houver alguma tabela que eu possa consultar ou mesmo se houver um arquivo .sql que o SQL Server está escondendo que eu possa cavar, isso seria ótimo.

sql-server sql-server-2012

3 respostas

  1. Best Answer

    sys.sql_expression_dependenciesdeve fornecer o que você deseja se não quiser pesquisar a definição de cada objeto (especialmente quando as referências podem ou não ter prefixos de esquema).

    Acho que você pode obter o que deseja com a seguinte consulta:

    SELECT 
    o.name AS ReferencingObject, 
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';
    • 16

  2. Acesse o adorável sys.sql_modulesDMV:

    USE tempdb;
GO

IF ( OBJECT_ID( 'tempdb.dbo.#t_ObjectsOfInterest', 'U' ) IS NULL )
BEGIN
    CREATE TABLE #t_ObjectsOfInterest
    (
        [DBName]                    NVARCHAR( 128 ),
        [ObjectName]                NVARCHAR( 128 ),
        [Definition]                NVARCHAR( MAX )
    );
END;

EXECUTE dbo.sp_MSForEachDB @command1 = '
    USE [?];

    INSERT  INTO #t_ObjectsOfInterest ( [DBName], [ObjectName], [Definition] )
    SELECT  DB_NAME(), OBJECT_NAME( object_id ), definition
    FROM    sys.sql_modules
    WHERE   definition LIKE ''%%dbo.ufn%%'';';
GO

SELECT  *
FROM    #t_ObjectsOfInterest;
GO

--DROP TABLE #t_ObjectsOfInterest;

    Para verificar quaisquer referências de trabalho:

    SELECT  sj.name, *
FROM    msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs
    ON  sjs.job_id = sj.job_id
WHERE   sjs.command LIKE '%dbo.ufn%';

    Você pode não pegar tudo, como referências de código de aplicativo em linha ou outras referências de pacote SSIS/SSRS, mas isso deve pelo menos remover os catálogos reais de suas preocupações.

    Além disso, se for "uma dessas" funções, você também pode usar sys.computed_columnscomo sys.sql_modulesacima para encontrar quaisquer tabelas usando a função em questão como uma coluna computada.

    • 0

  3. Isso parece funcionar muito bem.

    DECLARE @DBs TABLE (
                    DatabaseName NVARCHAR(128),
                    ID INT IDENTITY(1,1)
                    )
DECLARE @Objs TABLE (
                    DatabaseName NVARCHAR(128), 
                    Loc NVARCHAR(128), 
                    SchemaObjectName NVARCHAR(128), 
                    ID INT IDENTITY(1,1)
                    )
                    
DECLARE @LookingFor     NVARCHAR(4000)=N'%SearchValueGoesHere%',
        @intCnt         INT = 1,
        @RecCnt         INT = 0,
        @DatabaseName   NVARCHAR(128),
        @sql            NVARCHAR(max)=N''

INSERT INTO @dbs(DatabaseName)
SELECT 
    name 
FROM 
    sys.databases d
WHERE 
    1=1
AND
    d.name NOT IN ('msdb','master','tempdb','model')
    
SET @recCnt = @@ROWCOUNT

WHILE @intCnt<=@RecCnt
BEGIN
    SELECT 
        @databasename = databasename 
    FROM 
        @dbs 
    WHERE 
        id = @intCnt
    PRINT @databasename
    SET @sql = N'USE ' +@databasename+ N' 
    SELECT 
        '''+@DatabaseName+N''' Dbase, ''SQL Modules'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName 
    FROM 
        sys.sql_modules 
    WHERE 
        definition LIKE ''' +@LookingFor +N''' 
    UNION
    SELECT 
        '''+@DatabaseName+N''' Dbase, ''Computed Columns'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName 
    FROM 
        sys.computed_columns 
    WHERE 
        definition LIKE ''' +@LookingFor+N''' 
    UNION
    SELECT 
        '''+@DatabaseName+N''' Dbase, ''Check ConstraINTs'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName 
    FROM 
        sys.check_constraints
    WHERE 
        definition LIKE ''' +@LookingFor+N''' 
    UNION
    SELECT 
        '''+@DatabaseName+N''' Dbase, ''Default ConstraINTs'' Loc, CONCAT(OBJECT_SCHEMA_NAME(object_id), N''.'', OBJECT_NAME(object_id)) AS SchemaObjectName 
    FROM 
        sys.default_constraints
    WHERE 
        definition LIKE ''' +@LookingFor +N''''
    
    PRINT @sql
    
    INSERT INTO @Objs (DatabaseName, Loc, SchemaObjectName)
    EXEC sp_executesql @sql
    
    SET @intCnt += 1
END

SELECT * FROM @objs
    • -1

relate perguntas