Determine Column Names from a dynamic SQL statement in TSQL

[Funka]

Greetings,

I have a stored procedure that accepts a dynamic sql statement, and I need a way to determine the column names of the recordset generated by this sql statement. (Easy enough to do with ADO using the myRS.Fields collection, but I would like to do this entirely in my stored procedure.)

My ideas are to open a cursor using the dynamic sql statement supplied, and then examine the cursor to determine these column names. I unfortunately do not have the required permission to select from the master database table 'syscursorcolumns' which seems like the easiest way. My last resort is to try and clobber my way through the result cursor of sp_describe_cursor_columns, but this is turning out to be really ugly.

My stored proc is pasted below. A sample "@mySql" var might look something like this:

SELECT a as 'WidgetID'
, b as 'WidgetCount'
, c as 'WidgetPrice'
FROM myTable

...and what i want is to insert into another table the values "WidgetID", "WidgetCount", and "WidgetPrice", NOT the actual values represented by a, b, and c. So if someone passes another sql statment using entirely different column names, then i can capture those as well, etc...

(we can safely assume there will never be more than 9 column names in any sql statement, and i want these 9 columns to go into a single row of another table.)

The stored proc needed can be really simple:

CREATE PROCEDURE myProc
@mySql varchar(2000)
AS
BEGIN

DECLARE @ColumnName1 varchar(50)
DECLARE @ColumnName2 varchar(50)
DECLARE @ColumnName3 varchar(50)
DECLARE @ColumnName4 varchar(50)
DECLARE @ColumnName5 varchar(50)
DECLARE @ColumnName6 varchar(50)
DECLARE @ColumnName7 varchar(50)
DECLARE @ColumnName8 varchar(50)
DECLARE @ColumnName9 varchar(50)

-- some magic stuff here to retrieve column names into my declared vars

-- insert column names from the sql statement into a table
INSERT INTO myColumnsTable (Col1, Col2, ...)
VALUES (@ColumnName1, @ColumnName2, ...)

-- insert values from sql statement into another table.
-- (done easily enough with standard cursor)

END

 

[tlbroadbent]

Why don't you just parse the SQL statment and extract the column names? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Funka]

Hi, thanks for your reply:

Parsing the SQL statement sounds like a good idea, but in order to do so thoroughly and successfully I'm not sure if T-SQL can provide me the correct tools? (e.g., can you do RegExps in T-SQL?)

here's how i have thought your suggestion out over the weekend in psuedocode:

1. Strip anything from the input between "/*" and "*/"
2. Strip input from "--" to the end of a line
3. convert all remaining carriage returns to spaces
4. Grab all text between SELECT and FROM, not including those words, and discard everything else
5. Split this text into separate strings, delimited by commas.
6. analyze each of these separate strings:
6A. if contains the word "AS" then remaining (possibly quoted or non-quoted) word is the fieldname.
6B. if does not contain the word "AS" then the string itself is the fieldname, stripping anything left of a period found (i.e., get rid of any tablename or alias qualifier)

This sounds simple enough to do in VB or PHP, (and if i am going to do it in one of these i might as well use ADO's RS.Fields() collection), but i'm not sure how i could make this work practically in TSQL.

i am wondering if PATINDEX and a WHILE loop can provide me with enough functionality to do this, as it just seems a bit limited for my purposes at hand unless i'm missing some other ways of doing this.

Thanks again,
-f!

 

[tlbroadbent]

The following code does most of the steps in your pseudocode. It doesn't strip out comments. I'm sure you can handle that.

CREATE PROCEDURE myProc
@mySql varchar(2000)
AS

Declare
@cols char(450), @col1 varchar(50),
@col2 varchar(50), @col3 varchar(50),
@col4 varchar(50), @col5 varchar(50),
@col6 varchar(50), @col7 varchar(50),
@col8 varchar(50), @col9 varchar(50)

Set @cols = space(450)
Set @MySql=
'Select column1 As Thingy, column3 as foo, column7 As Widget, column22, ' +char(13)+char(10)+
'Column4 AS morestuff From dbo.mytable where column1=67'

Select @MySql=replace(replace(Left(@MySql, charindex('From', @MySql)-1),char(13)+char(10),''),', ',',')+','
Print @MySql
Declare @cs int, @ce int, @lc int
Set @lc=0
Set @cs=charindex('Select ', @MySql) + 7
Set @ce=charindex(',',@MySql,@cs+1)

While @ce > 0 Or @lc = 9
Begin
Set @col1 = Substring(@MySql,@cs,@ce-@cs)
If charindex(' As',@col1)>0 Set @col1 = left(@col1, charindex(' As', @col1)-1)
Set @cols = stuff(@cols, @lc*50+1, 50, @col1)
--Print @cols
Set @lc=@lc+1
Set @cs=charindex(',', @MySql, @ce)+1
Set @ce=charindex(',', @MySql, @cs + 1)
End

Set @col1=substring(@cols,1,50)
Set @col2=substring(@cols,51,50)
Set @col3=substring(@cols,101,50)
Set @col4=substring(@cols,151,50)
Set @col5=substring(@cols,201,50)
Set @col6=substring(@cols,251,50)
Set @col7=substring(@cols,301,50)
Set @col8=substring(@cols,351,50)
Set @col9=substring(@cols,401,50)

Insert YourColumnsTbl (Col1, Col2, ..., Col9)
Values (@col1, @col2, ..., @col9) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.