locked
View the Dependencies of a Table: T-SQL code does not work RRS feed

  • Question

  • View the Dependencies of a Table in MSDN shows  alternative methods of getting this information: SSMS and T-SQL code. However, none of sys.sql_expression_dependencies,  sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities works for me.

    Consider the following code:

    CREATE TABLE dbo.master(
        c1 INT not null
        CONSTRAINT PK_master_c1 PRIMARY KEY(c1)
    );
    GO
    CREATE TABLE dbo.detail(
        c1 INT
        CONSTRAINT FK_master_c1_detail_c1 FOREIGN KEY(c1) references dbo.master(c1)
    );
    GO

    In SSMS, if I right click the dbo.master table and select "View Dependencies", it shows that dbo.detail depends on dbo.master and dbo.master depends on nothing. However, when I run the query:

    SELECT * FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'dbo.master');

    to view the objects that depend on dbo.master, the result set is empty. The following queries also return nothing:

    SELECT * FROM sys.dm_sql_referencing_entities('dbo.master', 'OBJECT');
    SELECT * FROM sys.dm_sql_referenced_entities('dbo.master', 'OBJECT');
    SELECT * FROM sys.dm_sql_referencing_entities('dbo.detail', 'OBJECT');
    SELECT * FROM sys.dm_sql_referenced_entities('dbo.detail', 'OBJECT');

    Questions:

    1) Does the MSDN article explain the queries correctly? It says that to show the objects that depend on Production.vProductAndDescription, you should write "WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');" and to show the objects on which
    Production.vProductAndDescription depends, you should write "WHERE referenced_id = OBJECT_ID(N'Production.Product');". Surely it should be the other way round?

    2) Is it wrong that these queries are returning nothing? In my example, dbo.detail is not a view based on dbo.master. Neither is it a stored procedure that refers to dbo.master. It is a table with a foreign key that references dbo.master. Still, MSDN suggests sys.sql_expression_dependencies as an alternative to SSMS, so I was expecting it to show the same information.

    I am running SQL Server 2012 Express 64-bit on Windows 8. My login (Windows Authentication) is a member of the sysadmin server role and is the creator of the database in which I am working. This database has just the two tables in it.

    Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
        Dec 28 2012 20:23:12
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

    • Moved by Tom Phillips Thursday, May 23, 2013 9:23 PM TSQL question
    Thursday, May 23, 2013 8:58 PM

Answers

  • Have you tried with - sp_MSdependencies (depend on master)

    EXEC sp_MSdependencies N'dbo.master', NULL, 1315327


    Narsimha

    • Marked as answer by johnbrown105 Friday, May 24, 2013 12:13 AM
    Thursday, May 23, 2013 9:47 PM
  • EXEC sp_MSdependencies N'dbo.master', null, 1053183 
    --master doesn't depend on any table
    EXEC sp_MSdependencies N'dbo.detail', null, 1053183 
    --detail depends on master table


    Narsimha

    • Marked as answer by johnbrown105 Friday, May 24, 2013 12:13 AM
    Thursday, May 23, 2013 10:34 PM

All replies

  • You cannot depend on sysdepends.  There are many reasons for it to be invalid.

    Please see:

    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

    Thursday, May 23, 2013 9:23 PM
  • Have you tried with - sp_MSdependencies (depend on master)

    EXEC sp_MSdependencies N'dbo.master', NULL, 1315327


    Narsimha

    • Marked as answer by johnbrown105 Friday, May 24, 2013 12:13 AM
    Thursday, May 23, 2013 9:47 PM
  • Two things, Tom:

    1)  I don't agree that this is a Transact-SQL question.  This issue is not about the syntax and semantics of T-SQL; it is more about how SQL Server metadata is organised and retrieved.

    2) That is a looong article,and I have not read it thoroughly yet. However, if the system tables and/or views are so unreliable, shouldn't MSDN suggest something different? Remember, *I* did not make up the query with sys.sql_expression_dependencies.

    Thursday, May 23, 2013 10:10 PM
  • That works. Thanks, Narshima. Does it also show me the objects on which a table depends? Just yes or no, I will read up on sp_MSdependencies later.
    Thursday, May 23, 2013 10:20 PM
  • EXEC sp_MSdependencies N'dbo.master', null, 1053183 
    --master doesn't depend on any table
    EXEC sp_MSdependencies N'dbo.detail', null, 1053183 
    --detail depends on master table


    Narsimha

    • Marked as answer by johnbrown105 Friday, May 24, 2013 12:13 AM
    Thursday, May 23, 2013 10:34 PM
  • Hello Narsimha,

    Sorry I mangled your name, although I see that you sign it as Narsimha but your profile has it as Naarasimha. Anyway, you will see that I marked your posts as answers since  sp_MSdependencies gives the expected answer. Of course, I have not checked to see if anyone has reported that sp_Msdpendencies is unreliable.

    Friday, May 24, 2013 12:19 AM
  • One last thing, Tom:

    Could you specificaly answer question 1 from my original post, which was:

    1) Does the MSDN article explain the queries correctly? It says that to show the objects that depend on Production.vProductAndDescription, you should write "WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');" and to show the objects on which
    Production.vProductAndDescription depends, you should write "WHERE referenced_id = OBJECT_ID(N'Production.Product');". Surely it should be the other way round?

    Friday, May 24, 2013 12:22 AM
  • Well, <blank>, (for some reason your name is blank) if the undocumented procedure works better than the documented ones, I will probably have to take my chances. However, I still need to check if any of the measures in the looong article work in my case.
    Saturday, May 25, 2013 12:38 AM
  • Well, <blank>, (for some reason your name is blank) if the undocumented procedure works better than the documented ones, I will probably have to take my chances. However, I still need to check if any of the measures in the looong article work in my case.

    Very strange, that was my reply but the name it's showing as MigrationUser 1. Looks like it's a bug!

    To your question, please go through this link - http://www.sqlservercentral.com/articles/Stored+Procedures/62868/


    Narsimha

    Saturday, May 25, 2013 1:25 AM
  • I did not observe this thread until now.

    The story of dependency information in SQL Server is fairly complicated and unfortunately, not always a happy one.

    To start with, we need to distiguish between different kind of dependecies. Your example is the relation between two table, and in this case, SQL Server tracks the information in a fully reliable way. Simply because it has to. In your example, you may not drop master because of the referencing foreign key from the detail table. This information is not tracked in sys.sql_dependencies or sys.sql_expression_dependencies but in sys.foreign_keys and sys.foreign_key_columns.

    sys.sql_dependencies and sys.sql_expression_dependencies track information like "which procedures refers to this table"? None of them are fully reliable. In sys.sql_dependencies, dependencies are tracked on object id, which means that if you drop and recreate a table, you lose all information. sys.sql_expression_dependencies track information by name, which means that it does not have the same problem. Both have problems tracking dependencies when queries in a stored procedure includes a temp table.

    I stick to the older sys.sql_dependencies, because with this view it is possible to overcome the temp-table problem, although it's simple to get there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 25, 2013 11:50 AM
  • > we need to distinguish between different kind of dependencies.

    My post was motivated by the MSDN article View the Dependencies of a Table.  As far as I know, the SSMS method shows all dependencies, and recursively too. That is, it shows me that a stored procedure calls another stored procedure that references a table that depends on another table. Naturally, I expected the T-SQL method to show the same information as the SSMS method, since it is proposed as an alternative. You are say9ing that the suggested method will ignore foreign key relationships which are no less important than other dependencies. Therefore the article is incorrect or incomplete.

    Also on the topic of the accuracy of the MSDN article:
    1) Does it explain the queries correctly? It says that to show the objects that depend on Production.vProductAndDescription, you should write "WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');" and to show the objects on which
    Production.vProductAndDescription depends, you should write "WHERE referenced_id = OBJECT_ID(N'Production.Product');". Surely it should be the other way round?

    Saturday, May 25, 2013 3:40 PM
  • No, the topic in Books Online does not seem to be overly accurate. My focus, however, was more on trying to explain how it actually works.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 25, 2013 5:42 PM
  • So, Erland, what do you recommend? So far, sp_MSdependencies seems to be the way to go, undocumented or not.
    Sunday, May 26, 2013 9:45 AM
  • I looked at the source code, and this is an old procedure that uses the compatibility views that refelect the system tables in SQL 2000 and earlier. These views do not support features added in later versions of SQL Server.

    Specifically, consider:

    CREATE TABLE albin(a int NOT NULL)
    go
    CREATE PROCEDURE albin_sp @a int AS
    INSERT albin (a) VALUES (@a)
    go
    DROP TABLE albin (a int NOT NULL)
    CREATE TABLE albin (a smallint NOT NULL)

    sp_MSdependencies will not list this dependency. sys.sql_expression_dependencies will.

    So there is no one-stop shopping. If you want to know what SSMS does, use Profiler to find out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 26, 2013 10:33 AM
  • In response to your question

    1)  I don't agree that this is a Transact-SQL question.  This issue is not about the syntax and semantics of T-SQL; it is more about how SQL Server metadata is organised and retrieved.

    I moved it to TSQL as it was a request to query the metadata.  Allow me rephrase your question.  "How do I query the metadata to find dependancies between objects?"

    As Erland said, you can get data about some dependencies.  However, some things, like stored procedures, functions, etc you cannot reliably retrieve, even though it infers you can.

    You also did not tell us what your purpose is for finding these dependencies? 

    Tuesday, May 28, 2013 2:49 PM
  • I moved it to TSQL as it was a request to query the metadata.  

    Which is not a question about writing T-SQL. It was a perfectly legitmiate question for the Database Engine forum. It's not a very good question for the T-SQL forum.

    Personally, I have never liked this business with moving threads around. It's very confusing. Not the me for me who answers questions. I start to answer a question, and the poster responds back. And then some moderator gets a whim to move the thread to some forum I don't follow, and the poster does not get any help.

    It is evitable that there is some overlap between the T-SQL forum and the Database Engine forum, but it only adds to confusion if you move posts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 28, 2013 10:17 PM
  • I maintain that a valid T-SQL question would be:

    I have written a query Q that is supposed to return S1, but it returns S2 instead. Why? See DDL and sample data for table T below.

    OR

    Vendor V's SQL has a cool  feature F that T-SQL does not have. How can I write a T-SQL query that does what F does? Right now I am using temp tables and cursors.

    Clearly there is a difference of opinion, so we will just have to agree to disagree.

    I don't have a particular reason for finding the dependencies. As I said in the original post, I was reading an MSDN article that showed the SSMS way and the T-SQL way to view dependencies and they did not agree. This naturally required an explanation.

    Thursday, May 30, 2013 8:17 AM