February 4, 2016 at 3:24 pm
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?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply