(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 17-------------------------------------------------- List of tables referenced in the view By: srinivasma_exceldbp Date: March 6, 2014, 1:24 am --------------------------------------------------------- /* If you require to obtain list of tables in a view exec sp_refreshview 'viewname' exec sp_lib_TablesintheView 'ViewName' exec dbo.sp_lib_TablesintheView */ CREATE Procedure dbo.sp_lib_TablesintheView @viewname varchar(200) = NULL AS SELECT vObj.name AS ViewName, vObj.id AS ViewID, vObj.xtype AS ViewType, dep.depid AS DependentID, dep.depnumber AS Dependentnumber, tObj.name AS TableName, col.colid AS ColumnID, col.name AS columnName FROM sysobjects vObj LEFT OUTER JOIN sysdepends dep ON vObj.id = dep.id LEFT OUTER JOIN sysobjects tObj ON dep.depid = tObj.id LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND tObj.id = col.id WHERE vObj.xtype = 'V' And vObj.category = 0 AND ( ( vObj.name = @viewname ) OR ( @viewname IS NULL) ) ORDER BY vObj.name, tObj.name, col.name go *****************************************************