Pass column name which contains SQL query to sp_describe_first_result_set [closed]

by

I found below to extract tables from SQL queries
Get a list of tables accessed by a SELECT statement

But in my case SQL queries are stored in a column so I want to pass column name to get tables from SQL queries.

In my case I have table which having column statement contains SQL queries so I want to extract tables from that statement and store into another column in same table

  • Please ensure you provide a self-contained minimal reproducible example with your own sample data, desired results and attempt. Links to relevant questions are fine, so long as they are only supporting material and your question still makes sense if the links break.

    – 

  • You have to repeat your question 3 times to get the magic out

    – 

You can use the dm_exec_describe_first_result_set function to get the needed data:

declare @t table (sql nvarchar(max))

insert into @t (sql)
select 'select 1 as test'
union all
select 'select * from sys.objects'

select *
from @t t
cross apply sys.dm_exec_describe_first_result_set(t.sql, N'',1) r

The last parameter to dm_exec_describe_first_result_set specifies if you want to get the table names as well.

Leave a Comment