All dependencies and entities on sql server

Shri 21 Reputation points
2020-11-15T20:20:21.55+00:00

Hi Expert,
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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,786 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
532 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.