DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 17--------------------------------------------------
       List of tables referenced in the view 
   DIR 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
       *****************************************************
       Page 1 of 1