CODEWITHSUNDEEP
sqlsql-serverstored-procedures
owl
owl

Reputation: 154

How to find all columns that are used in stored procedures of database?

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

Answers (2)

Alex
Alex

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

TheGameiswar
TheGameiswar

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

Related Questions