Post reply

Find all objects that use this view?

  • March 10, 2010 at 9:17 am

    #216472

    Morning-

    Anyone know of a way to find all sprocs or views in a database that use a specific view or table in the FROM clause?

    Thanks...

    Jason

  • March 10, 2010 at 9:56 am

    #1131516

    Try this

    SQL Server 2008 Books Online (November 2009)

    http://msdn.microsoft.com/en-us/library/ms190464.aspx

    Think it has all that your need.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 10, 2010 at 7:09 pm

    #1131747

    I use this simple query to find this.

    select distinct B.name ,

    case type when 'P' then 'Stored procedure'

    when 'FN' then 'Function'

    when 'TF' then 'Function'

    when 'TR' then 'Trigger'

    when 'V' then 'View'

    end

    from syscomments A (nolock)

    join sysobjects B (nolock) on A.Id = B.Id

    where A.text like '%Objetc_Name%'

    I hope help you.

    Fabrício França Lima
    MCITP – Database Administrator
    https://www.fabriciolima.net/Blog

  • March 11, 2010 at 1:48 am

    #1131865

    Fabricio is right, his suggestion works for me also.

    With one difference that I was only using the syscomments table not joining to sysobjects in order to get a better output.

    Unfortunately there is not a built-in function that gives such a list except selecting from the syscomments.

    Eralper

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • March 11, 2010 at 2:06 am

    #1131876

    You might find these articles by Phil Factor useful:

    Finding stuff part1 & part2

    Nigel
    How to post questions

  • March 11, 2010 at 2:53 am

    #1131893

    Nigel,

    Good point and nice links, but they don't cover the new system views in 2008.

    See Understanding SQL Dependencies in Books Online.

    The new system views:

    sys.sql_expression_dependencies

    sys.dm_sql_referencing_entities

    sys.dm_sql_referenced_entities

    ...represent the best solution from Microsoft so far, rivalling third-party solutions to this long-standing problem.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • March 11, 2010 at 8:46 am

    #1132099

    Thanks Paul,

    They could come in really handy. Oh, if only I had 2008, 😀 and the time to play.

    I'd not even noticed this was in a 2008 forum :blush:

    Nigel
    How to post questions

  • March 11, 2010 at 8:48 am

    #1132103

    nigel. (3/11/2010)

    They could come in really handy. Oh, if only I had 2008, 😀 and the time to play.

    2008 Dev Edition $50ish.

    Free time...priceless :laugh:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • March 12, 2010 at 4:52 pm

    #1133069

    This query is very useful for me.

    Fabrício França Lima
    MCITP – Database Administrator
    https://www.fabriciolima.net/Blog

  • March 12, 2010 at 5:18 pm

    #1133073

    FabricioLimaDBA (3/10/2010)

    I use this simple query to find this.

    select distinct B.name ,

    case type when 'P' then 'Stored procedure'

    when 'FN' then 'Function'

    when 'TF' then 'Function'

    when 'TR' then 'Trigger'

    when 'V' then 'View'

    end

    from syscomments A (nolock)

    join sysobjects B (nolock) on A.Id = B.Id

    where A.text like '%Objetc_Name%'

    I hope help you.

    This little script works fine.

    One concern with this script is the use of objects that are only available for backwards compatability. These objects are not guaranteed to be present in future versions. I would recommend upgrading the script to use the views and DMVs for 2005 and 2008.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 12, 2010 at 6:26 pm

    #1133078

    That's OK. I work with Sql Server 2000 and 2005 yet. I will make the upgrade.

    Thank you.

    Fabrício França Lima
    MCITP – Database Administrator
    https://www.fabriciolima.net/Blog

  • March 13, 2010 at 11:26 am

    #1133239

    FabricioLimaDBA (3/12/2010)

    That's OK. I work with Sql Server 2000 and 2005 yet. I will make the upgrade.

    Thank you.

    Something that I do is make it conditional. I run in a multi-version environment as well. Throw in a condition that checks for server version, if 2000 run this script - else run this script. Works great.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 13, 2010 at 11:53 am

    #1133245

    The 2005/2008 query that I use is:

    select OBJECT_NAME(object_id), *

    from sys.all_sql_modules

    where definition like '%<your search phrase goes here>%'

    If I want to limit the results to just views, proc, etc. I'll do a join to sys.views, sys.procedures, sys.triggers, etc.

    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

  • March 13, 2010 at 12:09 pm

    #1133248

    Thank you WayneS.

    Fabrício França Lima
    MCITP – Database Administrator
    https://www.fabriciolima.net/Blog

  • March 13, 2010 at 12:15 pm

    #1133250

    WayneS (3/13/2010)

    The 2005/2008 query that I use is:

    select OBJECT_NAME(object_id), *

    from sys.all_sql_modules

    where definition like '%<your search phrase goes here>%'

    If I want to limit the results to just views, proc, etc. I'll do a join to sys.views, sys.procedures, sys.triggers, etc.

    Thanks Wayne.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 14 (of 14 total)

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