Get alias column name of procedure in SQL

Question

I am using system procedure as follow to get Column Name from procedure.

exec Sp_depends @procedure-name

but it is not returning alias column name.

I have following query in procedure.

SELECT ID, E_No As E_No, Case When E_SAL BETWEEN 10000 AND 50000 THEN 1 ELSE 0 END E_ACTIVE
FROM E_MAST
WHERE E_SAL BETWEEN 10000 AND 50000"

I need all column , including alias column but i cannot return alias. How can i return alias column name from procedure?

current result is :

**name         type   updated selected  column**
dbo.E_MAST  user table  no      yes       ID
dbo.E_MAST  user table  no      yes       E_NO
dbo.E_MAST  user table  no      yes       E_SAL

i need this result :

**name         type   updated selected  column**
dbo.E_MAST  user table  no      yes       ID
dbo.E_MAST  user table  no      yes       E_NO
dbo.E_MAST  user table  no      yes       E_SAL
dbo.E_MAST  user table  no      yes       E_ACTIVE

It's not very clear to me what you want. Can you provide an example of how it should work? — Szymon, Mar 28 '14 at 05:40
the above query is in procedure for ex. [dbo].[sp_getempsal].. when i execute below query to get used column in procedure Exec sp_depends 'sp_getempsal' i can't get E_ACTIVE column name. — Ritesh Khatri, Mar 28 '14 at 05:44

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

sp_depends stored procedure is used to

Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure.

So it correctly identifies all the columns that your stored procedure depends on. E_ACTIVE is an alias, it's not a database object, so there cannot be a dependency on it.

You can also check this question and its answer: Retrieve column definition for stored procedure result set to see how you can get the metadata about a stored proc.

edited May 23 '17 at 12:28

Community

1
1

answered Mar 28 '14 at 05:52

Szymon

42,577
16
96
114

Please also note that `sp_depends` will be removed in the future so better do not rely on it in new code. – Szymon Mar 28 '14 at 05:52
thanks to reply dear. have any alternative way to get that column name ? – Ritesh Khatri Mar 28 '14 at 05:56
See the question I linked in my answer. – Szymon Mar 28 '14 at 05:59

Get alias column name of procedure in SQL

1 Answers1