Reputation: 154
How to see list of all columns and tables used by all stored procedures (+views and functions) in a database.
EXAMPLE:
create procedure proc as
select tab1.a,tab2.d,tab2.e
from tab1
join tab2 on tab1.b = tab2.b
where tab1.c = 'filter'
end
output:
tab1.a
tab1.b
tab1.c
tab2.b
tab2.d
tab2.e
I need to see ALL columns referenced by ANY code in database. Thank you.
Comments are welcome.
Upvotes: 3
Views: 4936
Reputation: 21766
You can use sys.dm_sql_referenced_entities
, check the documentation for further details
SELECT referenced_entity_name + '.' + referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.Proc1', 'OBJECT')
WHERE referenced_minor_name IS NOT NULL
Upvotes: 2
Reputation: 28860
create proc usp_test1
as
begin
select name,object_id from test
end
create proc usp_test2
as
begin
select * from test1
end
executing below code gives me
SELECT DISTINCT
O.name SP_Name,T.name Table_Name,c.name Field_Name
FROM sys.sysdepends D
JOIN sys.sysobjects O ON O.id = D.id
JOIN sys.sysobjects T ON T.id = D.depid
JOIN sys.columns C ON C.column_id=d.depnumber
and C.object_id=D.depID
WHERE O.xtype = 'P'
SP_Name Table_Name Field_Name
usp_test1 test name
usp_test1 test object_id
usp_test2 test1 create_date
usp_test2 test1 is_ms_shipped
usp_test2 test1 is_published
usp_test2 test1 is_schema_published
usp_test2 test1 modify_date
usp_test2 test1 name
usp_test2 test1 object_id
usp_test2 test1 parent_object_id
usp_test2 test1 principal_id
usp_test2 test1 schema_id
usp_test2 test1 type
usp_test2 test1 type_desc
References: http://www.sqlservercentral.com/Forums/Topic1060325-149-1.aspx
Upvotes: 5