SQL Queries for finding dependent objects

USE ReportServer

GO

/* Using the below query to finding the Finods_Replica DB dependent SSRS reports in royaltyopsreports DB*/

/*Seached with Finods_replica keyword to findout the SSRS reports, weather the finods_replica DB tables are directly refering to the reports*/

;WITH Reports

AS

(

       SELECT NAME AS ReportName,

                     CONVERT(NVARCHAR(MAX),

                     CONVERT(XML,CONVERT(VARBINARY(MAX),Content))) AS ReportContent

       FROM [Catalog] WHERE NAME IS NOT NULL

)

SELECT * FROM Reports WHERE ReportContent LIKE '%Test%' ORDER BY 1

/**********Finding Dependents in Stored Procedures*************/

USE 

SELECT Name,OBJECT_DEFINITION(OBJECT_ID) [Definition]

FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Test.%'

/**********Finding Dependents in Synonyms *************/

SELECT Name,base_object_name FROM sys.synonyms WHERE base_object_name LIKE '%Test%'

/**********Finding Dependents in Views *************/

SELECT Name,OBJECT_DEFINITION(OBJECT_ID) [Definition] FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Test%'

/**********Finding Dependents in SSIS Packages in MSDB(Integration Services)*************/

USE msdb

GO

;WITH SSISPackages

AS

(

SELECT [name] AS SSISPackageName

, CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata))) AS SSISPackageXML

FROM msdb.dbo.sysssispackages

)

SELECT * FROM SSISPackages WHERE SSISPackageXML LIKE '%Test%' ORDER BY 1