FindDependenatObjects

FindDependenatObjects

 

--This script can be used to determine the SPs which are using a specific table

CREATE TABLE #tblOut

( id int identity(1,1),

tSchema varchar(100),

dependency varchar(1000)

)

DECLARE @tbl TABLE

( id int identity(1,1),

tSchema varchar(20),

tName varchar(50)

)

DECLARE @schema varchar(20)

DECLARE @table varchar(50)

DECLARE @cnt int = 0

INSERT INTO @tbl (tschema, tname)VALUES

('Controversy','GeneralLedgerDetail')

,('Common','AccountingDocumentItem')

,('Excise','DartAccountingDocumentItem')

,('Controversy','VendorPayment')

,('Common','AccountingDocument')

,('Excise','DartAccountingDocument')

,('Excise','ComplianceMaster')

,('Excise','Invoice')

,('Domestic','SpecialDepreciationDetail')

,('Domestic','FixedAssetDetail')

,('Controversy','GeneralLedgerDetailCount')

,('Controversy','Depreciation')

,('Controversy','AssetMaster')

,('Common','ExcDartUIParams_Hold')

,('Domestic','BSISReport')

,('Common','wrkParADI_YYYYMM')

,('Excise','ReportingJurisdiction')

,('Controversy','VendorAddress')

,('Controversy','GeneralLedgerSummary')

,('Controversy','Vendor')

SELECT @cnt = MAX(id)

FROM @tbl

WHILE (@cnt > 0)

BEGIN

SELECT @schema = tSchema, @table= tName

FROM @tbl

WHERE id = @cnt

INSERT INTO #tblOut (tSchema, dependency)

SELECT @schema + '.' + @table as tbl,

(

SELECT DISTINCT Object_Name(id) + ', '

FROM sys.syscomments

WHERE text like'%'+@schema+'.'+ @table +'%'

OR text like'%'+@schema+'].['+ @table +'%'

OR text like'%'+@schema+'.['+ @table +'%'

OR text like'%'+@schema+'].'+ @table +'%'

FOR xml path('')

)

SELECT @cnt = @cnt -1

END

SELECT * FROM #tblOut

DROP TABLE #tblOut