List all the associated tables for a view

,

I had recent request for a script to list all the views for a database along with the associated tables the columns depend on. It is a rather simple script but as I have not seen a similar one, I thought I would share it with my fellow dbas. It returns information about views specific to the database it is run in. If any of the view columns are linked to an outside database or server, it may return null data for those fields, as the depend data might not be stored locally in within the database. I have also excluded system views from showing up but should you with to include system views, simply remove "And vObj.category = 0" from the script.

Create Procedure ViewAssociations
AS
SELECT  vObj.name AS vName, vObj.id AS vID, vObj.xtype AS vType, 
	dep.depid, dep.depnumber, tObj.name AS tName, col.colid,
	col.name AS cName
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
ORDER BY vObj.name, tObj.name, col.name

Rate

Share

Share

Rate