Post reply

t-sql

  • December 28, 2010 at 8:41 am

    #242807

    For 'selected' stored procedure(s) in 'selected' databases, I would like to know the t-sql statement(s) to find all the tables and views used used.

    Better yet for all the 'stored procedures' in a database, I would like to t-sql statments to see what tables and views the stored procedures are using.

  • December 28, 2010 at 9:32 am

    #1266791

    You can use the Dynamic Management Object sys.dm_sql_referenced_entities to gather the tables & views referenced by a procedure. That assumes that the procedure is not dynamic SQL though.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 28, 2010 at 9:44 am

    #1266795

    This will get a list of all datasets (tables/views) referenced in the definition for all procedures, with some limitations:

    1. If you have 2 tables named "Table1" and "Table11", and the code is referencing "Table11", you will get a match for both tables.

    2. As Grant mentioned, if the table name is a variable for making dynamic sql, it won't find it.

    3. It's not exactly what I would call fast.

    Edit: 4. It only matches up tables/views from the same database. If the procedure references a table/view in a different database, it won't find it.

    Edit2: 5. It will find matches in comments also.

    WITH DataSets AS

    (

    -- get list of all user-defined tables (type=U) and views (type=V)

    SELECT name, type

    FROM sys.objects so

    WHERE type LIKE '[UV]'

    AND is_ms_shipped = 0

    )

    SELECT [ProcName] = so.name,

    [DataSet] = ds.name

    FROM sys.objects so

    JOIN sys.all_sql_modules sasm

    ON so.object_id = sasm.object_id

    -- join sys.objects to sys.all_sql_modules

    -- (where the procedure definition is stored).

    JOIN DataSets ds

    ON sasm.definition like '%' + ds.name + '%'

    -- join the above datasets, for just the procedures

    -- where the dataset name is in the definition.

    WHERE so.type = 'P' -- just do procedures

    -- for a specific procedure, just add:

    -- AND so.name = '<MyProcedureName>'

    ;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 28, 2010 at 11:52 am

    #1266859

    I forgot to mention that I am using sql server 2005 standard edition. I can not find sys.dm_sql_referenced_entities in the that edition. Is there something else I can use?

  • December 28, 2010 at 11:53 am

    #1266861

    Thanks! This sql works very well!

  • December 28, 2010 at 12:03 pm

    #1266865

    wendy elizabeth (12/28/2010)

    I forgot to mention that I am using sql server 2005 standard edition. I can not find sys.dm_sql_referenced_entities in the that edition. Is there something else I can use?

    Sorry. I don't see in the documentation that this is a 2008 DMO, but it might be.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 28, 2010 at 9:22 pm

    #1267004

    wendy elizabeth (12/28/2010)

    Thanks! This sql works very well!

    It may not matter but it looks like that code will find table and view names in comments, as well, which may produce "false alarms". If you can tolerate that possibility then, I agree... that code works very well, indeed! The advantage that code has over other methods is that it will also find the table and view names in dynamic SQL, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 28, 2010 at 9:48 pm

    #1267009

    Jeff Moden (12/28/2010)

    wendy elizabeth (12/28/2010)

    Thanks! This sql works very well!

    It may not matter but it looks like that code will find table and view names in comments, as well, which may produce "false alarms". If you can tolerate that possibility then, I agree... that code works very well, indeed!

    Good point Jeff. I've edited that post to reflect that it will find matches in comments.

    The advantage that code has over other methods is that it will also find the table and view names in dynamic SQL, as well.

    Unless the table/view name is a parameter and is added to the dynamic sql without ever exposing the name.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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