title: Getting SQL Server Cross database Dependencies
body: <p>SQL Server Version - 2008 R2</p>
  
  <p>I am working on evaluating a DMS solution, with an objective of taking over maintenance. The original solution has one central database, that has data pertaining to the manufacturer. It also has one database for each dealer, which means there are a lot of cross database dependencies. </p>
  
  <p>The problems:</p>
  
  <ul>
  <li>No DB documentation</li>
  <li>No code comments</li>
  <li>Lots of heaps</li>
  <li>No standard object naming conventions</li>
  <li>The central DB has 460+ tables and 900+ SProcs, in addition to other
  objects</li>
  <li>Each dealer DB has 370+ tables and 2350+ SProcs, in addition to other
  objects</li>
  </ul>
  
  <p>As a first step, I am recommending a complete clean-up of the DB, for which it is critical to understand object dependencies, including cross database dependencies. I tried using Red Gate's solution, but the output is way too voluminous. All I want is a list of objects in the databases that do not have any dependencies - they neither depend on other objects, nor are there any objects that depend on them.</p>
  
  <p>Here is the script I have used to get a list of dependencies:</p>
  
  <pre><code>SELECT
  DB_NAME() referencing_database_name,
  OBJECT_NAME (referencing_id) referencing_entity_name,
  ISNULL(referenced_schema_name,'dbo') referenced_schema_name,
  referenced_entity_name,
  ao.type_desc referenced_entity_type,
  ISNULL(referenced_database_name,DB_NAME()) referenced_database_name
  FROM sys.sql_expression_dependencies sed
  JOIN sys.all_objects ao
  ON sed.referenced_entity_name = ao.name 
  </code></pre>
  
  <p>I will be creating a table - Dependencies - into which I will be inserting this result set from each DB. As a next step, I will also be creating another table - AllObjects- which will contain a list of all objects in the Databases. Here is the script to do this:</p>
  
  <pre><code>SELECT
  DB_NAME() DBName,
  name,
  type_desc
  FROM sys.all_objects
  WHERE type_desc IN
  (
  'VIEW',
  'SQL_TABLE_VALUED_FUNCTION',
  'SQL_STORED_PROCEDURE',
  'SQL_INLINE_TABLE_VALUED_FUNCTION',
  'USER_TABLE',
  'SQL_SCALAR_FUNCTION'
  )
  </code></pre>
  
  <p>Now, a list of name from this table, that do not appear in the referenced_entity_name column in the dependencies table should give a list of objects that I am looking for.</p>
  
  <pre><code>SELECT
  AO.DBName,
  AO.name,
  AO.type_desc
  FROM AllObjects AO
  LEFT OUTER JOIN Dependencies D ON
  D.referenced_database_name = AO.DBName AND
  D.referenced_entity_name = AO.name AND
  D.referenced_entity_type = AO.type_desc
  WHERE 
  D.referenced_database_name IS NULL AND
  D.referenced_entity_name IS NULL AND
  D.referenced_entity_type IS NULL
  </code></pre>
  
  <p>Now the questions:</p>
  
  <ol>
  <li>Some object dependencies seem to be missing in the output. What am I
  missing?</li>
  <li>How do I validate that my findings are correct?</li>
  <li>I mean is there a different way to do this, so I can compare the
  results and double check?</li>
  </ol>
  
  <p>Thanks in advance,</p>
  
  <p>Raj</p>