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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment