locked
Show all View/Procedures/Functions that reference a column? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Greetings!

    Our ERP provider recently released a major upgrade that changed a lot of the SQL table structures in their database. We have hundreds of views and reports that reference some of these columns and tables.

    The process of finding all of the objects in our custom database and SRS reports has been monumental. I was wondering, is there any function or way that we can select a column in a table (or a table for that matter) and list every object that references that table or column?

    For example:

    Let's say we have views and procedures that reference Table A, column X. Let's also assume for complexity that column X isn't a unique name, and serves a different function in different table, so you can have A.X, B.X, Y.X.

    Now let's say A.X has been renamed to A.Y.

    How can I generate a list of every object that lists everything that ever touched A.X, but not B.X?

    Is this even possible?

    Thanks!
    Database Administrator
    Tuesday, April 19, 2011 3:05 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    You can use the sql_dependencies view to get all the dependents.

    SELECT OBJECT_NAME(referenced_major_id) AS ReferingObject, 
	(SELECT o.type_Desc FROM sys.objects o
		WHERE o.object_id = d.referenced_major_id) AS ReferingObjType,
CASE WHEN referenced_minor_id <> 0 THEN COL_NAME(referenced_major_id, referenced_minor_id)
		ELSE '' END AS RefColumnName,
OBJECT_NAME(object_ID) AS ReferencedObject,
(SELECT o.type_Desc FROM sys.objects o
		WHERE o.object_id = d.object_id) AS ReferedObjType 
fROM sys.sql_dependencies d
    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by Naomi N Friday, April 22, 2011 7:01 PM
    • Marked as answer by Kalman Toth Monday, April 25, 2011 8:58 AM
    Tuesday, April 19, 2011 3:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    To Find all the objects referring to a table/view/sp - I would right click on the table and VIEW DEPENDENCIES.

     

    -Komal

    • Marked as answer by Kalman Toth Monday, April 25, 2011 8:58 AM
    Tuesday, April 19, 2011 3:19 PM

All replies

  • Question
    Sign in to vote
    2
    Sign in to vote

    You can use the sql_dependencies view to get all the dependents.

    SELECT OBJECT_NAME(referenced_major_id) AS ReferingObject, 
	(SELECT o.type_Desc FROM sys.objects o
		WHERE o.object_id = d.referenced_major_id) AS ReferingObjType,
CASE WHEN referenced_minor_id <> 0 THEN COL_NAME(referenced_major_id, referenced_minor_id)
		ELSE '' END AS RefColumnName,
OBJECT_NAME(object_ID) AS ReferencedObject,
(SELECT o.type_Desc FROM sys.objects o
		WHERE o.object_id = d.object_id) AS ReferedObjType 
fROM sys.sql_dependencies d
    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by Naomi N Friday, April 22, 2011 7:01 PM
    • Marked as answer by Kalman Toth Monday, April 25, 2011 8:58 AM
    Tuesday, April 19, 2011 3:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    To Find all the objects referring to a table/view/sp - I would right click on the table and VIEW DEPENDENCIES.

     

    -Komal

    • Marked as answer by Kalman Toth Monday, April 25, 2011 8:58 AM
    Tuesday, April 19, 2011 3:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the replies, guys! The one issue I have with this is it is bound to the database the object is located in. I probably should have added one more vital piece of information: The views in Database B might reference columns in Database A. While both of these replies work well for all objects contained in the existing database, it can't identify columns whose source is outside of it. Thanks, and sorry for forgetting to mention it!
    Database Administrator
    Tuesday, April 19, 2011 3:27 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    I dont think that is possible using t-sql. If so i would be interested in know the code too. But you might try using some utilities like redgate SQL Compare which can compare two db on different server and get the dependent objects too. It worth trying, i am not sure if you will get what you want.

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, April 19, 2011 3:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks dbp...

    Your view, and even right click -> Show dependencies seems inaccurate as well, and maybe you know why.

    One of the views in our system shows in you query that it depends on another view that has an 'x' at the start of the view name. We use that to denote views that we are developing or temporary views.

    However, when I go to alter the view, there is no part in the query that references that temporary view. Now, it is practice here that when we agree that the view is adequate, we will rename it without the x, so temporary view xView_Sales_Region might be renamed to View_Sales_Region.

    Is this an issue where SQL only remembers the original name, and not the current one?

    Database Administrator
    Tuesday, April 19, 2011 3:44 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    No that is not an issue. Once you change your object name the system view will be refreshed to hold the new values. It should not reflect the old ones. Also try using sys.sysdepends.
    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, April 19, 2011 3:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The following article deals with object dependencies:

    http://www.sqlusa.com/bestpractices2008/object-dependency/

     

    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Monday, April 25, 2011 9:00 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    This is an excellent query for 2008. However, our production servers are, for the time being, on 2005.

    Thank you though!
    Database Administrator
    Monday, April 25, 2011 12:27 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you are not getting all objects usihg sp_depends then you can use below query to get all objects

    to get all tables:

    select table_name from information_schema.columns where column_name like '%somecolumn_name%'

    To get all SPs, Views, Functions etc:

    select name from sys.objects where object_definition(object_id) like '%somecolumn_name%'

     

    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Monday, April 25, 2011 12:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A good suggestion, and I already built something very similiar to this. It even connects to our SRS server and analyizes what queries the reports use.

    The problem is that string-based matches aren't relible. If I have tablex.columna and tablex.columnab, any search for tablex.columna will yield two results, one of which will be incorrect. More than that, if you have an underscore (_) as part of a table/field name, the LIKE statement doesn't match it as a literal character.

    It's surprising to me that Microsoft hasn't made more progress for native impact analysis tools beyond dependency views and procedures. I was trying to create a .NET parser, using SMO libraries - I figured if Management Studio can detect what column and table you are referencing when you use design view, I should be able to extract it. Then I noticed that even THAT has limitations. For example, there's no way to determine through dependencies or in studio that a column from a table is used in a case statement or other logical operation if it's not directly selected. So given the difficulty of figuring that out for still inaccurate results, I have decided to stop pursing it. Guess we'll have to do it the old fashion way and force people to keep up their own documentation. Sure, it will slow things down, but it's always worth it at times like this.

    Thank you for the suggestions everyone!

    Database Administrator
    Monday, April 25, 2011 12:55 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The problem is that string-based matches aren't relible. If I have tablex.columna and tablex.columnab, any search for tablex.columna will yield two results, one of which will be incorrect. More than that, if you have an underscore (_) as part of a table/field name, the LIKE statement doesn't match it as a literal character.

    You can enclose the _ in brackets to have interpreted literalily:

    %somecolumn[_]name%

    It's surprising to me that Microsoft hasn't made more progress for native impact analysis tools beyond dependency views and procedures. I was trying to create a .NET parser, using SMO libraries - I figured if Management Studio can detect what column and table you are referencing when you use design view, I should be able to extract it. Then I noticed that even THAT has limitations. For example, there's no way to determine through dependencies or in studio that a column from a table is used in a case statement or other logical operation if it's not directly selected. So given the difficulty of figuring that out for still inaccurate results, I have decided to stop pursing it. Guess we'll have to do it the old fashion way and force people to keep up their own documentation. Sure, it will slow things down, but it's always worth it at times like this.

    It is possible to build complete dependency information SQL 2005, as long you as 1) don't use dynamic SQL 2) don't have cross-db dependencies. But it is by no means easy. I am able to do it, because I have the tool. (Which I have written myself.) When it loads a stored procedure, it extracts all temp table definitions and creates them before the procedure is loaded. Without this the dependency information can never get complete because of deferred name resolution.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 25, 2011 1:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Gah.. Never even thought to add the brackets. Well that will help resolve that issue! Thanks!

    And yes, as I stated before, I have built a rough system for determining dependency information, but one of the big problems with it is, as you pointed out, cross-db dependency. Almost all of our objects reference objects from another database because we keep all of our reporting objects seperate from our ERP application, as that database is straight vanilla and modified frequently by our provider.

    Database Administrator
    Monday, April 25, 2011 2:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    > And yes, as I stated before, I have built a rough system for determining dependency information, but one of the big problems with it is, as you pointed out, cross-db dependency. Almost all of our objects reference objects from another database because we keep all of our reporting objects seperate from our ERP application, as that database is straight vanilla and modified frequently by our provider.

    For cross-db dependency the new stuff in SQL 2008 is better. Unfortunately, there are also a lot of shortcomings with the new dependency functions as well.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 25, 2011 6:43 PM