script to list all object dependencies including cross database

• ebooklub

  Hall of Fame

  Points: 3905

  More actions

  February 4, 2016 at 3:24 pm

  #324622

  Hi,

  I am trying to list all object dependencies and getting confused result

  listing stored procedures

  Scenario 1

  EXEC sp_MSdependencies @object, null, 1315327

  EXEC sp_MSdependencies @object, null, 1053183

  work fine but does not list cross database reference objects

  Scenario 2

  CREATE TABLE #databases(

  database_id int,

  database_name sysname

  );

  -- ignore systems databases

  INSERT INTO #databases(database_id, database_name)

  SELECT database_id, name FROM sys.databases

  WHERE database_id in(1,4 ,db_id('mydb'))

  DECLARE

  @database_id int,

  @database_name sysname,

  @sql varchar(max);

  CREATE TABLE #dependencies(

  referencing_database varchar(max),

  referencing_schema varchar(max),

  referencing_object_name varchar(max),

  referenced_server varchar(max),

  referenced_database varchar(max),

  referenced_schema varchar(max),

  referenced_object_name varchar(max)

  );

  WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN

  SELECT TOP 1 @database_id = database_id,

  @database_name = database_name

  FROM #databases;

  SET @sql = 'INSERT INTO #dependencies select

  DB_NAME(' + convert(varchar,@database_id) + '),

  OBJECT_SCHEMA_NAME(referencing_id,'

  + convert(varchar,@database_id) +'),

  OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),

  referenced_server_name,

  ISNULL(referenced_database_name, db_name('

  + convert(varchar,@database_id) + ')),

  referenced_schema_name,

  referenced_entity_name

  FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

  EXEC(@sql);

  DELETE FROM #databases WHERE database_id = @database_id;

  END;

  SET NOCOUNT OFF;

  SELECT *

  FROM #dependencies where referencing_object_name = @object and referencing_database =@database and referencing_schema = @schema

  result of query is listing dependencies including cross database BUT is different from GUI Result taken from SSMS-> SP->Show dependencies ->Objects on which depend

  How do you script you object dependencies?

  I also thought ...If (SSMS -SP->Show dependencies ->Objects on which depend) provide up to date info about dependencies , would it be easier to get same result using Powershell and SMO?