8

I have been given the task to move reports from SSRS to our internal ASP.Net application and this has involved me re-writing a few functions and a stored procedure to both improve performance, make them output in forms better consumed by the application and make any requested additions to the reports.

What I also need to do is remove the old functions and procedures from the system as I go, before I do this though I want to make sure it will not break anything else. Is there a way I can go though all the precedures and functions to see if any of them contain references to the ones I will be removing?

There are thousands of these to go though and I would rather not have to open each one at a time in SSMS to look. So if there is some table I can query or even if there is a .sql file that SQL server is hiding that I could dig though, that would be great.

1
  • Red gate sql search is good free tool to do this – Biju jose Nov 4 '16 at 17:14
13

sys.sql_expression_dependencies should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).

I think you can get what you want with the following query:

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';
1
  • 2
    The other problem with brute force parsing is false positives (e.g. the module name is in a comment, a string literal, or part of another object name). But both methods are vulnerable to false negatives (e.g. the call to the module is constructed with dynamic SQL). – Aaron Bertrand Nov 4 '16 at 20:01
0

Hit up the lovely sys.sql_modules DMV:

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;

To check any job references:

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%';

You may not catch everything, such as in-line app code references or other SSIS / SSRS package references, but that should at least remove the actual catalogs from your concerns.

Also, if it's "one of those" functions, you can also use sys.computed_columns like sys.sql_modules above to find any tables using the function in question as a computed column.

2
-1

This appears to work quite well.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.