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
