I am trying to find the dependent jobs, linkked server, users,ssis packages,source target mapping,db stats,sql files, impacted files, impacted codes, ssis package variable, ssis config tables, ssis packages, sql agent schedule on sql server database
I have tried below mentioned query
SELECT DISTINCT
DB_NAME() AS [Database]
,SCHEMA_NAME(od.[schema_id]) AS [Schema]
,OBJECT_NAME(d1.referencing_id) AS dependent_object
,od.[type_desc] AS dependent_object_type
,COALESCE(d1.referenced_server_name, @@SERVERNAME) AS referenced_server_name
,COALESCE(d1.referenced_database_name, DB_NAME()) AS referenced_database_name
,COALESCE(d1.referenced_schema_name, SCHEMA_NAME(ro.[schema_id])) AS referenced_schema_name
,d1.referenced_entity_name
,d1.referenced_id
,DB_NAME(ro.parent_object_id) AS referenced_object_db
,ro.[type_desc] AS referenced_object_type
,d1.referencing_id
,SCHEMA_NAME(od.[schema_id]) AS SchemaDep
FROM sys.sql_expression_dependencies d1
LEFT OUTER JOIN sys.all_objects od
ON d1.referencing_id = od.[object_id]
LEFT OUTER JOIN sys.objects ro
ON d1.referenced_id = ro.[object_id]
How can I get the remaining entities in a query? Any help appreciated
Because all those objects and processes you list are not stored within SQL Server, there's not a query that's going to return them all. A given SSIS package can be executed from all sorts of different locations. SQL files could be stored anywhere (unless you're just talking about the data & log files) and aren't marked in the database. A single query to rule them all just isn't possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply