none
How would I find out refrence objects?

    Question

  • How can I find out

    1) Table that has been referenced in any stored proc. In other words I have table named Customer. There are 50 stored proc
    in the database. I need to find out which of those stored proc reference Customer table?

    2) I also need to find out column named 'Age' in the same table 'Customer' is being referenced in which database objects?
    Function, stored proc, or in any other places.

    Thank you

    Wednesday, December 31, 2008 1:51 AM

Answers

  • Here is one more method to do your search, it won't be limited to Stored Procs...which could be helpful if you want to search other programmable objects stored in your database.

    DECLARE @SearchString NVARCHAR(MAX)

    SET @SearchString = 'ENTER_SEARCH_TEXT_HERE'

    SELECT [name], OBJECT_DEFINITION(OBJECT_ID([name]))

    FROM sys.all_objects

    WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @SearchString + '%'

    --Optional to limit search results....

    --AND [is_ms_shipped] = 0 --Only search user & post SQL release objects

    --Change the above value to 1 to only include objects provided by Microsoft with the release of SQL Server

    --AND [type] = 'P' --Search only Stored Procedures

    --AND [type] <> 'V' --Exclude Views

    --See referenced article for listing of additional object types


    I wrote a post discussing it: http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html

    HTH,
    James
    Knowledge is the first step towards success. Little knowledge creates big ideas. ---------------------------- http://sqln.blogspot.com
    Wednesday, December 31, 2008 5:11 PM

All replies

  • use this..

     

    sp_depends 'table/objectname'

     


    I am Back..!
    Wednesday, December 31, 2008 4:19 AM
  • You can also have a look at sys.sql_expression_dependencies.

    HTH.
    Aaron Alton | thehobt.blogspot.com
    Wednesday, December 31, 2008 4:40 AM
    Moderator
  • I could see the list of procs and function that reference particualr table by doing

    sp_depends 'table_name'

    BUT

    when I tried to do

    sp_depends 'table_name/Col1' It gave me error. I need to find out if this column is being used in any of the objects

    Thanks

    Wednesday, December 31, 2008 3:33 PM
  • Here is one more method to do your search, it won't be limited to Stored Procs...which could be helpful if you want to search other programmable objects stored in your database.

    DECLARE @SearchString NVARCHAR(MAX)

    SET @SearchString = 'ENTER_SEARCH_TEXT_HERE'

    SELECT [name], OBJECT_DEFINITION(OBJECT_ID([name]))

    FROM sys.all_objects

    WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @SearchString + '%'

    --Optional to limit search results....

    --AND [is_ms_shipped] = 0 --Only search user & post SQL release objects

    --Change the above value to 1 to only include objects provided by Microsoft with the release of SQL Server

    --AND [type] = 'P' --Search only Stored Procedures

    --AND [type] <> 'V' --Exclude Views

    --See referenced article for listing of additional object types


    I wrote a post discussing it: http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html

    HTH,
    James
    Knowledge is the first step towards success. Little knowledge creates big ideas. ---------------------------- http://sqln.blogspot.com
    Wednesday, December 31, 2008 5:11 PM
  • DBA James said:

    Here is one more method to do your search, it won't be limited to Stored Procs...which could be helpful if you want to search other programmable objects stored in your database.

    DECLARE @SearchString NVARCHAR(MAX)

    SET @SearchString = 'ENTER_SEARCH_TEXT_HERE'

    SELECT [name], OBJECT_DEFINITION(OBJECT_ID([name]))

    FROM sys.all_objects

    WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @SearchString + '%'

    --Optional to limit search results....

    --AND [is_ms_shipped] = 0 --Only search user & post SQL release objects

    --Change the above value to 1 to only include objects provided by Microsoft with the release of SQL Server

    --AND [type] = 'P' --Search only Stored Procedures

    --AND [type] <> 'V' --Exclude Views

    --See referenced article for listing of additional object types


    I wrote a post discussing it: http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html

    HTH,
    James
    Knowledge is the first step towards success. Little knowledge creates big ideas. ---------------------------- http://sqln.blogspot.com



    James,

    This is a very good solution you have posted.  Many people can use this type of code in a variety of situations.  I would like to make one small suggest though.  In the current code you have posted, you are simply displaying the code as 1 line.  I would take the code a step further and use the new built in xml capabilities of SQL 2005 and 2008 to make the text ledgible.  By introducing the text as XML, you allow the user to click on the objects definition and launch it in another window, with majority of the formatting in tact.  Take a look at the code below.

    DECLARE @SearchString NVARCHAR(MAX)   
    SET @SearchString = 'SearchString'   
     
    SELECT   
        [name],  
        (  
            SELECT   
                OBJECT_DEFINITION(obj2.object_id) AS [text()]  
            from sys.all_objects obj2  
            where obj2.object_id = obj1.object_id  
            FOR XML PATH(''), TYPE  
        ) AS Obj_text,  
        [type] as ObjType   
    FROM sys.all_objects obj1  
    WHERE OBJECT_DEFINITION(obj1.object_id) LIKE '%' + @SearchString + '%'   
    --Optional to limit search results....   
    AND [is_ms_shipped] = 0 --Only search user & post SQL release objects   
    --Change the above value to 1 to only include objects provided by Microsoft with the release of SQL Server   
    --AND [type] = 'v' --Search only Stored Procedures   
    --AND [type] <> 'V' --Exclude Views   
     
    • Edited by Adam HainesModerator Wednesday, December 31, 2008 7:35 PM Removing view filter because the op is looking through sps
    Wednesday, December 31, 2008 6:12 PM
    Moderator
  • Adam,

    Thanks for the compliment, the intentions of the code I originally developed was to allow for finding dependencies...but, then as I started to develop this I found that there are sometimes other objects I want to search also. It's also been a great way to search constraints to ensure that I don't break them when modifying a Stored Proc.

    Thanks for the suggestion, that is a great addition to the code!!

    A quick correction in your posted code, you modified the WHERE clause [type] to search for views, but left the comment stating it was searching for Stored Procedures:

    AND [type] = 'v' --Search only Stored Procedures

    I'm only mentioning this correction to ensure that others reading this post are not confused or find the code doesn't give the expected results. The correct [type] to search for is P, the line should read:

    AND [type] = 'p' --Search only Stored Procedures

    I'll be sure to include this in an update post on my blog, giving you the credit of course, for this great enhancement.

    Thanks!
    James
    Knowledge is the first step towards success. Little knowledge creates big ideas. ---------------------------- http://sqln.blogspot.com
    Wednesday, December 31, 2008 7:20 PM
  • DBA James said:

    Adam,

    Thanks for the compliment, the intentions of the code I originally developed was to allow for finding dependencies...but, then as I started to develop this I found that there are sometimes other objects I want to search also. It's also been a great way to search constraints to ensure that I don't break them when modifying a Stored Proc.

    Thanks for the suggestion, that is a great addition to the code!!

    A quick correction in your posted code, you modified the WHERE clause [type] to search for views, but left the comment stating it was searching for Stored Procedures:

    AND [type] = 'v' --Search only Stored Procedures

    I'm only mentioning this correction to ensure that others reading this post are not confused or find the code doesn't give the expected results. The correct [type] to search for is P, the line should read:

    AND [type] = 'p' --Search only Stored Procedures

    I'll be sure to include this in an update post on my blog, giving you the credit of course, for this great enhancement.

    Thanks!
    James


    Knowledge is the first step towards success. Little knowledge creates big ideas. ---------------------------- http://sqln.blogspot.com



    Your right :).   i was playing with the type filter and forgot to comment it out.  I will modify the post to remove the filter all together.
    Wednesday, December 31, 2008 7:34 PM
    Moderator