Post reply

Any simple way to determine which which Procedures, Views, and Functions call a table?

  • June 8, 2011 at 10:46 am

    #241833

    Hi,

    I'm trying to find a more efficient and accurate way to determine which Procedures, Views, and Functions reference any given table. I have this query which does it to some degree:

    SELECT DISTINCT s.name AS [Schema],

    o.name AS [Object],

    o.type AS [ObjectType],

    o.crdate

    FROM syscomments c

    INNER JOIN sysobjects o ON c.id=o.id

    INNER JOIN sys.schemas s ON s.schema_id = o.uid

    WHERE REPLACE(REPLACE(c.TEXT,'[',''),']','') LIKE '%Info.Customer%'

    And this takes into account whether or not the brackets were used, which depending on who wrote the object sometimes they were used and sometimes not. The problem I'm running into is we often have tables where the schema and first part of the table name are the same for multiple tables. For example Info.Customer, Info.CustomerAddress, Info.CustomerXRef, etc. So when searching for Info.Customer this will return more than just that table.

    I also tried using LIKE '%Info.Customer % with as space before the trailing % but again this doesn't always work because often the next character is a tab. I could I guess check for the ASCII character for TAB as well...

    Does TSQL have a more efficient way other than scanning the text to get this? Something that might parse the object in the backend and list all objects that would be affected by the object? I wasn't sure if such logic existed.

    Thanks for any suggestions...

  • June 8, 2011 at 10:52 am

    #1335815

    There were a lot of bugs in sql 2000 with dependencies but this is something that has been fixed in 2005 AFAIK.

    If you need something better than right click on all objects you can check out red-gate's dependency tracker which puts it all in a nice graphical view for you.

    http://www.red-gate.com/products/sql-development/sql-dependency-tracker/

  • June 8, 2011 at 10:52 am

    #1335816

    there is a view you can use, that is mostly correct:

    SELECT OBJECT_NAME(REFERENCING_ID),* FROM sys.sql_expression_dependencies

    WHERE OBJECT_NAME(REFERENCING_ID = 'YourProcName'

    issues can arise sometimes, becasue you can create a view that references tables that do not exist at the time of creation...so if the proc was created before the tables it references, no entry will be created.

    you can fix that by ALTER PROC...with the same ooriginal definition of the code body, and it would populate/repopulate dependancy information again.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 8, 2011 at 11:04 am

    #1335831

    Nice, thanks for the suggestions. I modified my original query to take into account pretty much everything I could think of that might proceed a table name, and it matches the results from sys.sql_expression_dependencies:

    SELECT DISTINCT s.name AS [Schema],

    o.name AS [Object],

    o.type AS [ObjectType],

    o.crdate

    FROM syscomments c

    INNER JOIN sysobjects o ON c.id=o.id

    INNER JOIN sys.schemas s ON s.schema_id = o.uid

    WHERE REPLACE(REPLACE(c.TEXT,'[',''),']','') LIKE '%Info.Customer[' + CHAR(10) + CHAR(11) + CHAR(13) + CHAR(32) + ']%'

    ORDER BY o.name

    I'll do more testing to see if this matches sys.sql_expression_dependencies, but if sys.sql_expression_dependencies will do the trick then that's much simpler 😉

    Thanks -

Viewing 4 posts - 1 through 3 (of 3 total)

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