none
Get column names from query text RRS feed

  • Question

  • I'm trying to find a way to extract the column names from a text of a T-SQL query.

    As an example, if I'm looking at the following query...

    SELECT
    ContactID,
    CASE WHEN Title IS NOT NULL THEN Title + ' ' ELSE '' END + 
    	FirstName + 
    	CASE WHEN MiddleName IS NOT NULL THEN ' ' + MiddleName ELSE '' END + 
    	' ' + LastName + 
    	CASE WHEN Suffix IS NOT NULL THEN ' ' + Suffix ELSE '' END AS FullName,
    -- Phone,
    EmailAddress
    FROM Person.Contact
    


    ... I want the output to be...

    ContactID   

    FullName   

    EmailAddress


    "Phone" would be excluded because it has been commented out in the text.

    I'm still early in the prototyping phase so I'm open any ideas.

    If anyone is interested, the long range goal is to emulate a Data Driven Subscription using only SQL Server standard and step one is to populate the "DataSettings" column in the Subscriptions column.

    Thanks in advance,


    Jason Long
    Tuesday, November 29, 2011 4:42 PM

All replies

  • Your statement is correct.
    What do you want to ask?
    Shatrughna.
    Tuesday, November 29, 2011 6:09 PM
  • I'm actually treating the SQL Statement as a test string and I need to pull out the column names.

    Take this as test data...

    USE tempdb
    GO
    
    IF OBJECT_ID('DataSettingSQL') IS NOT NULL DROP TABLE DataSettingSQL
    CREATE TABLE DataSettingSQL (
    	SubscriptionID UniqueIdentifier NOT NULL Primary Key,
    	SQL_Text nVarChar(MAX) NOT NULL)
    
    DECLARE @sid UniqueIdentifier, @sql nVarChar(Max)
    SET @sid = '0D5671CC-D7B5-465C-A747-126F7DD67531'
    SET @sql = 'SELECT
    ContactID,
    CASE WHEN Title IS NOT NULL THEN Title + '' '' ELSE '''' END + 
    	FirstName + 
    	CASE WHEN MiddleName IS NOT NULL THEN '' '' + MiddleName ELSE '''' END + 
    	'' '' + LastName + 
    	CASE WHEN Suffix IS NOT NULL THEN '' '' + Suffix ELSE '''' END AS FullName,
    -- Phone,
    EmailAddress
    FROM Person.Contact'
    
    INSERT INTO DataSettingSQL VALUES 
    (@sid, @sql)
    
    SELECT * FROM DataSettingSQL
    


    I need to look at the value in SQL_Text and output the column names...

    ContactID   

    FullName   

    EmailAddress

     


    Jason Long
    Tuesday, November 29, 2011 7:06 PM
  • Well, take a look at SET FMTONLY setting in BOL. If you're looking at writing a T-SQL language parser, then there are some DLLs that already exist (used for formatting the text). Say, see
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 29, 2011 7:10 PM
    Moderator
  • Naomi,

    The SET FMTONLY looks like it might be a step in the right direction but I'm looking to get the column names returned as rows as opposed to column heading, so that they can be inserted, as rows, into another table.

    Any idea as to how I can take the FMTONLY output and "unpivot" it into rows?

    As for VB approach... That would put me WAY out of my element. I was really hoping to to find a pure T-SQL solution.

    Is there any way to capture the column names from the SQL Server parser as it parses the query?


    Jason Long
    Tuesday, November 29, 2011 8:17 PM
  • Well, dynamic UNPIVOT is not too complicated, there are many samples in this forum. If you get the result into a table, then convert fields into rows is just by using dynamic SQL (you will need to convert the fields into sql_variant or varchar(max) - mvarchar(max) works for 90% of data types).

    Try searching this forum or see 3rd problem in this blog post

     

    Interesting T-SQL problems

    I don't know how to invoke parser directly - this may be an interesting question for a developer from MS who may know how to invoke the parser and if it's possible from the client's application.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 29, 2011 8:45 PM
    Moderator
  • This is not going to be a an easy thing to do with dynamic statements.  It might be worth your while to create views of the data.  You can then query view metadata to get the columns you need.  Another method is to put teh data into a temporary table and then get the column metadata by querying the temp table metadata.  Another solution is to query the cache and pull the column listing from the xml query plan.  Most of these solutions (all but the view one) require the code to be executed before the metadata can be retrieved.  This is something that the application tier can probably handle better than the database tier. 

    On another note, applications that have this level of flexibilty usually suffer in performance because of the dynamic nature of the queries and lack of query plan reuse.

     


    http://jahaines.blogspot.com/
    Tuesday, November 29, 2011 9:04 PM
    Moderator
  • I am thinking a CLR function using SET FMTONLY may work too. 
    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Tuesday, November 29, 2011 9:10 PM
  • Interesting ideas... I want to try a couple of things before I respond further.


    Jason Long
    Tuesday, November 29, 2011 9:29 PM
  • Naomi & Adam,

    Thanks for the ideas. I think both of your ideas got me moving in the right direction. Turns out using a dynamicly created view solved the problem (Naomi for the dynamic sql part and Adam for the View part).

    Here is what I got to work...

    DECLARE @sid UNIQUEIDENTIFIER, @sql VarChar(MAX), @createview VarChar(MAX)
    SET @sid = '0D5671CC-D7B5-465C-A747-126F7DD67535'
    SELECT @sql = SQL_Text FROM dbo.DataSettingSQL WHERE SubscriptionID = @sid
    SET @createview = 'CREATE VIEW TempX AS (' + @sql + ')'
    
    EXEC(@createview)
    
    INSERT INTO DataSettingColumns (SubscriptionID, ColumnName)
    SELECT @sid AS SubscriptionID, COLUMN_NAME AS ColumnName
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TempX'
    ORDER BY ORDINAL_POSITION
    
    DROP VIEW TempX
    
    SELECT * FROM DataSettingColumns
    


    ... and the output... 

    SubscriptionID                       ColumnName

    ------------------------------------ -------------

    0D5671CC-D7B5-465C-A747-126F7DD67535 ContactID

    0D5671CC-D7B5-465C-A747-126F7DD67535 EmailAddress

    0D5671CC-D7B5-465C-A747-126F7DD67535 FullName

     


    Jason Long
    Tuesday, November 29, 2011 10:00 PM
  • Being able to pull directly from the parser would be a more eligant solution but this works.

    I'll post the final SP script once I have it written.


    Jason Long
    Tuesday, November 29, 2011 10:05 PM
  • I'd be interested in seeing your final SP. Can you post?

    thanks,

    Friday, May 18, 2012 6:39 PM