finding Stored Procedure that use a specific table outside database?

  • Hi

    I use a DB that is used mainly for creating views and tables for another "data" db.

    A table has changed and I need to find all the Stored Procs and views that use this table

    found this code, but seem to only work withing the DB. Tried adding the full name 'OtherDB.dbo.ProgramSites'

    select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
    from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
    inner join sys.objects sp on sd.referencing_id = sp.object_id
    and sp.type in ('P', 'FN')
    where o.name = 'OtherDB.dbo.ProgramSites'
    order by sp.Name

    My other question is I figured to write a view that would get the info I need that was removed from the table and insert this view in all SP and Views to replace the original table. So  say the orginal name was dbo.ProgramSites and my view would be say "'dbo.NewProgramSites" , is there any way to run some sort of update to replace the table name with the view name, or would each SP need to be changed manually?

     

    Thanks

     

    • This topic was modified 1 year, 5 months ago by  jbalbo.
  • Hi There,

    I use the below code to find references to text (in this case a table name) in objects,

    Replace TEXT_TO_FIND with the table name you are looking for,

    It uses dynamic SQL and a Loop so is more than likely something that could be written better but it works for me and will hopefully help you out too,

    USE MASTER

    SET QUOTED_IDENTIFIER OFF

    DECLARE @SearchText AS VARCHAR(100)
    SELECT@SearchText = "TEXT_TO_FIND"

    CREATE TABLE #TEMP (ID INT IDENTITY(1,1), Name VARCHAR(100))
    INSERT INTO #TEMP (Name)

    --Loads All Available Databases
    SELECT"[" + name + "]" AS Name
    FROMSysDatabases
    WHEREDATABASEPROPERTYEX (Name, 'Status') = 'ONLINE'

    CREATE TABLE #RESULTS (ServerName VARCHAR(100), DatabaseName VARCHAR(100), ObjectName VARCHAR(100),
    ObjectType VARCHAR(100))

    DECLARE @LoopID AS INT, @MaxLoopID AS INT, @DatabaseName AS VARCHAR(100),
    @SQL AS VARCHAR(8000), @ServerName VARCHAR(100)

    SELECT @LoopID = 1, @SQL = ""
    SELECT @MaxLoopID = MAX(ID) FROM #TEMP
    SELECT @ServerName = CAST((SELECT SERVERPROPERTY("MachineName")) AS VARCHAR(100))

    --Searches Through All Databases For Search Text
    WHILE @LoopID <= @MaxLoopID
    BEGIN
    SELECT @DatabaseName = Name FROM #TEMP WHERE #TEMP.ID = @LoopID
    SELECT @SQL = @SQL + " INSERT INTO #RESULTS SELECT DISTINCT '" + @ServerName + "' AS ServerName,'" + @DatabaseName + "' AS DatabaseName, o.name, xtype FROM " + @DatabaseName + "..syscomments AS c JOIN" + @DatabaseName + "..sysobjects AS o ON c.[id] = o.[id] WHERE text LIKE ""%" + @SearchText + "%""" + CHAR(10) + ""
    SELECT @LoopID = @LoopID + 1
    END

    EXEC(@SQL)
    --SELECT @SQL

    SELECT * FROM #RESULTS

    DROP TABLE #TEMP
    DROP TABLE #RESULTS


    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Hi Shyan,

    Thank you very much, that worked great .

    No All I need to find out  is if'ts it's possible to update  these 496 SP and Views in an automated fashion 🙂

     

  • jbalbo wrote:

    Hi

    I use a DB that is used mainly for creating views and tables for another "data" db.

    A table has changed and I need to find all the Stored Procs and views that use this table

    Personally, I would use Redgate's "SQL Search" - a free plugin for SQL Server management studio - rather than rolling my own code to do that search...  but then I'm lazy.

     

    jbalbo wrote:

    All I need to find out  is if'ts it's possible to update  these 496 SP and Views in an automated fashion 🙂

    Well, you could write dynamic SQL to go through all the view definitions / SP definitions and alter them appropriately, but that's going to be deeply unpleasant, and possibly downright dangerous.  I strongly recommend testing on a private copy of the database before going anywhere near the development system (let alone test or production etc)

    Good luck!

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Sorry mate not that im aware of. Object explorer (F7) on the views/procs so you can highlight multiple objects then right click drop and create to new window and do a find and replace on the table name. Not ideal and prone to error so definately try on a non production database but may save some time

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • I am not sure how a 'table' change requires updating/modifying all stored procedures/views/functions/etc...  Do you mean to say that the old table is no longer used - and a new (different name) table is now being used?

    If so - then how sure are you that all of the columns being referenced by the views/procedures/functions in your report database have not also changed?  Is this new table a copy of the old table with all of the same columns?

    I am assuming that the reference to the old table is something like: DataDB.dbo.OldTableName

    To prevent this kind of issue in the future, you need to start utilizing SYNONYMS from the report database.  This will allow for easy updating of the synonym to reference a new name (or even a new database with the same structure).

    For example:

    CREATE SYNONYM DataDB.TableName FOR DataDB.dbo.TableName;

    Now in your code, instead of using 3-part naming you can use the synonym:

    SELECT ... FROM DataDB.TableName

    And if the name of the table changes - all you have to do is:

    DROP SYNONYM DataDB.TableName;

    CREATE SYNONYM DataDB.Table FOR DataDB.dbo.NewTableName;

    And now all of your code is referencing the new table with no additional changes needed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I believe OP was planning on replacing the name with a View but i agree a Synonym is a good way to go

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  •  

    Thanks for all the idea, first time I read about CREATE SYNONYM (I suck at this sometimes!!... lol)

    I'm going to learn about this and start applying it ...

    The problem is the one of the tables the vendor gave use had a derived field from two tables. They dropped that field on that table, so I figured to create a view in the report database, using the two tables, change all the SP's that use that table to point at the view.

     

     

  • Note that the "Text" column of sys.comments is only NVARCHAR(4000), so if you have a longer stored procedure that view would not see the entire definition.  For the complete definition, you should use sys.sql_modules "defintion" column, which is NVARCHAR(MAX).

  • jbalbo wrote:

    Thanks for all the idea, first time I read about CREATE SYNONYM (I suck at this sometimes!!... lol)

    I'm going to learn about this and start applying it ...

    The problem is the one of the tables the vendor gave use had a derived field from two tables. They dropped that field on that table, so I figured to create a view in the report database, using the two tables, change all the SP's that use that table to point at the view.

    Okay - this makes sense, but you now need to search for both the table in question and the column.  No need to modify the code if the column isn't referenced or used.

    Hopefully, the name of that column is unique enough to filter down to just the code that is referencing that column.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • First up, yes, a synonym is probably the easiest way to fix this.

    Second, source control is your bestest buddy on earth. If you get all your code that defines databases and database objects into source control, when you do need to replace some definition across a wide swath of code, a simple search & replace will do the trick. Then, if you're also using source control as your deployment mechanisms (and you should be), you're all set for the next step.

    Fundamentally, this is how all database development ought to be done (and only about 35-45% actually is).

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant/Jeff - won't any find/replace, in source control, via synonym or otherwise, have the same issues if the columns also changed? The only real method to make sure this works is to "automatically" assign lists out to developers, modify code and re-test, isn't it?

    Or am I missing something and doing things the hard way? (likely)

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • jonathan.crawford wrote:

    Grant/Jeff - won't any find/replace, in source control, via synonym or otherwise, have the same issues if the columns also changed? The only real method to make sure this works is to "automatically" assign lists out to developers, modify code and re-test, isn't it?

    Or am I missing something and doing things the hard way? (likely)

    "If it's not in source control, it doesn't exist"

    Start there and then manage all code through source control. Yes, if you modify columns, anything, there's nothing magical about how source control works. It just gives you a single point to easily modify a large number of objects. If I wanted to, for example, change the name of a column in 20 stored procedures, this is a trivial task in source control. Plus, I get the change tracking showing what changes i made, when etc.,. Then, if we're automating our dev, dev integration, continuous integration, qa, test, etc., we get to deploy and test that code on the fly.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply