Get list of table columns used in a stored procedures, view, functions

Question

I have a table which has 50 columns and I want to find out, what are the column names are actually used in stored proc, views, functions etc. Is there any query that I can used which will table name, used column name and the stored proc name where it is used.

I used following query which returns only the stored proc/view where this table is used. But I need the only used column name from this table, table name and stored proc name.

SELECT OBJECT_NAME(OBJECT_ID),
definition, *
FROM sys.sql_modules
WHERE definition LIKE '%' + 'tablename ' + '%'

Example:

Table Name:

Employee

Columns:

First Name
Last Name
Age
Email

If First name, Last name only is used in whole database, then it should only return those two columns.

Answer 1

Microsoft has a sp that shows the object dependency called: sp_depends

EXEC sp_depends 'Employee'

Will give you all the objects that depends on Employee

see link for more detail : https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql

Now to get the columns it has a function sys.dm_sql_referenced_entities() to list all columns.

SELECT
        referenced_id,
        referenced_entity_name AS table_name,
        referenced_minor_name  AS referenced_column_name,
        is_all_columns_found
    FROM
        sys.dm_sql_referenced_entities ('sp_MyProcOrView', 'OBJECT');

see link for more detail https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referenced-entities-transact-sql

You should be able to find the columns used with these two features.