none
Query Tables and Dependencies SQL Server 2012

    Question

  • I am trying to piece together a query that will return table names based on a schema, and also return all of the dependencies for the tables. The particular names of the schemas I am interested in are 'member','physician','shared','source', and 'sourceXRefMember'.

    I need to know which tables have no stored procedure dependencies.

    I've done some research on responses to this site, system tables, and have looked at tables and views in INFORMATION_SCHEMA. 

    I've tried the following query, but sys.sql_expression_dependencies does not return the table and schema if the table has no dependencies:

    SELECT distinct
    referenced_database_name AS database_name
        ,sc.[name] as SchemaName
        ,ob.[name] as tableName
    ,OBJECT_NAME(referencing_id) AS referencing_entity_name 

    from sys.objects ob
    left join  sys.sql_expression_dependencies sd
    on ob.object_id = sd.referenced_id
    left join sys.schemas sc
    on ob.schema_id = sc.schema_id
    where referenced_schema_name = 'physician'
    and referenced_database_name = 'MSIWStaging'
    and ob.type = 'u'

    --**********************

    What can I do to query tables, given a schema, that have no dependencies?

    Thank you for your help.

    cdun2


    Wednesday, June 12, 2013 4:42 PM

Answers

  • Try this, it returns all the table who don't have dependencies:

    SELECT * FROM SYS.objects OB LEFT JOIN sys.sql_expression_dependencies SD ON ob.object_id = sd.referenced_id WHERE TYPE_DESC='USER_TABLE' AND referencing_id IS NULL


    I think adding a condition for 'referenced_database_name' was a problem. Here is my modification so far:

    SELECT 
    sc.name as SchemaName
    ,ob.[name] as tableName 
    FROM SYS.objects OB 
    LEFT JOIN sys.sql_expression_dependencies SD 
    ON ob.object_id = sd.referenced_id 
    inner join sys.schemas sc
    on ob.schema_id = sc.schema_id
    WHERE TYPE_DESC='USER_TABLE' 
    AND referencing_id IS NULL
    and ob.schema_id in (27,28,29,30)

    Thanks.

    • Marked as answer by cdun2 Wednesday, June 12, 2013 5:38 PM
    Wednesday, June 12, 2013 5:38 PM

All replies

  • Try this, it returns all the table who don't have dependencies:

    SELECT * FROM SYS.objects OB LEFT JOIN sys.sql_expression_dependencies SD ON ob.object_id = sd.referenced_id WHERE TYPE_DESC='USER_TABLE' AND referencing_id IS NULL


    Wednesday, June 12, 2013 4:55 PM
  • Try this, it returns all the table who don't have dependencies:

    SELECT * FROM SYS.objects OB LEFT JOIN sys.sql_expression_dependencies SD ON ob.object_id = sd.referenced_id WHERE TYPE_DESC='USER_TABLE' AND referencing_id IS NULL


    I think adding a condition for 'referenced_database_name' was a problem. Here is my modification so far:

    SELECT 
    sc.name as SchemaName
    ,ob.[name] as tableName 
    FROM SYS.objects OB 
    LEFT JOIN sys.sql_expression_dependencies SD 
    ON ob.object_id = sd.referenced_id 
    inner join sys.schemas sc
    on ob.schema_id = sc.schema_id
    WHERE TYPE_DESC='USER_TABLE' 
    AND referencing_id IS NULL
    and ob.schema_id in (27,28,29,30)

    Thanks.

    • Marked as answer by cdun2 Wednesday, June 12, 2013 5:38 PM
    Wednesday, June 12, 2013 5:38 PM
  • Sorry, I've forgotten sys.schema:

    SELECT * FROM SYS.objects OB LEFT JOIN sys.sql_expression_dependencies SD ON ob.object_id = sd.referenced_id right join sys.schemas sc on ob.schema_id=sc.schema_id WHERE TYPE_DESC='USER_TABLE' and sc.sc.name='physician' and referencing_id is null


    • Edited by DIEGOCTN Wednesday, June 12, 2013 5:40 PM
    Wednesday, June 12, 2013 5:40 PM