asked
FInd the underlying tables assocaited with a view or a stored procedure in sql server.
I want to get the name of all tables associated with a view or a procedure. I have a query: select distinct v_obj.name as ViewName, t_obj.name as SourceTable, v_obj.xtype from sysobjects t_obj, sysobjects v_obj, sysdepends dep where
dep.id =
v_obj.id and dep.depid =
t_obj.id order by v_obj.name, t_obj.name This will return all the tables or views that are associated with a particular object. But the problem is that suppose we have a procedure that is associated with a view. In this case it will return the name of the view. I want is something that it will return the name of the tables associated with a procedure through a view. Example: If a procedure p is dependent upon a View v and table t1. and further v1 is dependent upon table t2 and t3 Then i want is t1, t2 and t3. Any help is appreciated. Thanks
@Kamal First of All, you have tagged it in SQL 2005/2008, but your query seems to be for SQL 2000? Do you need solution against SQL 2000? I can understand what you are trying to achieve but you must keep in mind that till SQL 2005 Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies. So you could not be sure that all your dependent objects would be in the list. For SQL 2005 you can run this kind of query ;WITH CTE AS (SELECT o.name , o.type_desc , p.name , p.type_desc , p.object_id FROM sys.sql_dependencies d INNER JOIN sys.objects o ON d.object_id = o.object_id INNER JOIN sys.objects p ON d.referenced_major_id = p.object_id UNION ALL SELECT o.name , o.type_desc , p.name , p.type_desc , p.[object_id] FROM sys.sql_dependencies d INNER JOIN CTE o ON d.object_id = o.object_id INNER JOIN sys.objects p ON d.referenced_major_id = p.object_id ) SELECT DISTINCT * FROM [CTE] But the game changes with SQL Server 2008. You can track the dependency no matter whether the referenced object exists or not. Below are some good hyperlinks to start with and you should be able to sort out your problem as it is much easier in SQL 2008 [
http://msdn.microsoft.com/en-us/library/ms345449(v=SQL.100).aspx][1] [
http://msdn.microsoft.com/en-us/library/bb677168(v=SQL.100).aspx][2] [1]:
http://msdn.microsoft.com/en-us/library/ms345449(v=SQL.100).aspx [2]:
http://msdn.microsoft.com/en-us/library/bb677168(v=SQL.100).aspx
