How to use STUFF and FOR XML PATH to get a list of column names IN ORDER BY COLUMN_ID

  • I am writing some fairly complicated dynamic SQL for a stored procedure that needs to be multi-purpose. As such, I want to flexibly insert the list of columns from my target table and from my source table into a string that I will then execute. I know how to do the concatenation with FOR XML PATH using STUFF to concatenate, but the results always come back in alphabetical order by column name, not by the order of the column in the table, which would be column_id. Here is what I am doing so far:

    declare @Cols nvarchar(max), @SQL nvarchar(max)
    SELECT c.column_id
      ,c.name 'ColumnName'
    into #tmp_columns
    FROM 
      sys.columns c
    INNER JOIN
      sys.types t ON c.user_type_id = t.user_type_id
    LEFT OUTER JOIN
      sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN
      sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE
      c.object_id = OBJECT_ID('MYTABLENAME')
        and (i.is_primary_key = 0 or i.is_primary_key is null)
    order by c.column_id asc  --- this doesn't seem to help 

    select @Cols = stuff((select ', ' + quotename(ColumnName) from (select distinct ColumnName from #tmp_columns) X
    for XML PATH('')),1,2,'')
    print @cols

    Once I can see the @Cols string come back in column_ID order I will know what to do to insert it in my dynamic SQL insert statement but I need this last bit of help. I haven't found anything like this on the web and using ORDER BY anywhere in this mess is not allowed.
    Thanks to all of you on these forums who help folks like me.

  • ok, so just to give anyone else with this problem the answer (yes I figured it out myself!!) all I had to do was to include the column_id in the statement:

    select @Cols = stuff((select ', ' + quotename(ColumnName) from (select distinct Column_ID, ColumnName from #tmp_columns) X 
    for XML PATH('')),1,2,'') 

    worked like a charm. I didn't realize that adding a column that is sorted properly in the first place provides the right output.

  • The ORDER BY used in filling the temp table has no effect on the column order in the XML result, because the DISTINCT keyword in your subquery (SELECT DISTINCT ColumnName FROM #tmp_columns) is forcing a sort by column name.  DISTINCT is also unnecessary, column names within tables have to be unique.  And why even use a subquery here?

    You can put ORDER BY Column_ID right before FOR XML PATH('') to enforce the desired order.  An explicit ORDER BY would be preferable to relying on the order imposed by DISTINCT, even if adding Column_ID to the subquery did fix the problem. 

    SELECT @Cols = STUFF((
        SELECT  ', ' + QUOTENAME(ColumnName)
        FROM #tmp_columns
        ORDER BY Column_ID
        FOR XML PATH('')), 1, 2, '')

    using ORDER BY anywhere in this mess is not allowed.


    What makes you say that?

  • I consider the following construct a bit simpler and does not require the temp table
    and output gets ordered by column_id as required
    code based on the OP. Does have the issue that if there are no indexes on the table it will output all columns of the table which I do not think is what is desired here. (but maybe I'm wrong)

    declare @Cols nvarchar(max)

    select @Cols = stuff(
           (select ', ' + quotename(c.name)
           from sys.columns c
           inner join sys.types t
            on c.user_type_id = t.user_type_id
           left outer join sys.index_columns ic
            on ic.object_id = c.object_id
            and ic.column_id = c.column_id
           left outer join sys.indexes i
            on ic.object_id = i.object_id
            and ic.index_id = i.index_id
           where c.object_id = base.object_id
            and (i.is_primary_key = 0 or i.is_primary_key is null)
           order by c.column_id
           for xml path ('')
           ), 1, 2, '')
    from (select object_id('MYTABLENAME') as object_id) base

    select @Cols

  • Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

  • Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

  • Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

  • Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Let me have a look Jonathan, I've had this discussion with Itzik Ben-Gan and few others
    😎

  • Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

  • Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AM

    drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

    I've not found that either but Microsoft hasn't conformed on it so I cannot recommend it. 
    😎

  • Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AM

    drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

    Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says

    To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 27, 2019 11:48 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AM

    drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

    Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says

    To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.

    Drew

    But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column(s) and it works.

  • Jonathan AC Roberts - Wednesday, February 27, 2019 11:55 AM

    drew.allen - Wednesday, February 27, 2019 11:48 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AM

    drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

    Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says

    To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.

    Drew

    But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column and it works.

    Is it?  They both use the quirky update method.  There are proven problems with the one, which indicates that there may also be problems with the other.  And just because it has performed a certain way in the past, doesn't mean that it will always perform a certain way in the future, especially since it is undocumented and unsupported, which means that MS won't test to see whether an update "breaks" this functionality.

    I see no benefit to using this method over the XML concatenation and lots of risk.  I simply don't think it's worth the risk.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 27, 2019 12:31 PM

    Jonathan AC Roberts - Wednesday, February 27, 2019 11:55 AM

    drew.allen - Wednesday, February 27, 2019 11:48 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AM

    drew.allen - Wednesday, February 27, 2019 10:00 AM

    Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AM

    Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AM

    Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PM

    Or something even simpler like this should work:
    DECLARE @Cols NVARCHAR(MAX)=''
    SELECT @Cols +=QUOTENAME(c.column_name)+', '
      FROM INFORMATION_SCHEMA.columns c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'
     ORDER BY c.ORDINAL_POSITION

    SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
    PRINT @Cols ;

    Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
    😎

    I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid. 
    Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?

    Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly.  You don't have everything in place that you need.

    Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.

    Drew

    I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
    Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?

    Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says

    To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.

    Drew

    But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column and it works.

    Is it?  They both use the quirky update method.  There are proven problems with the one, which indicates that there may also be problems with the other.  And just because it has performed a certain way in the past, doesn't mean that it will always perform a certain way in the future, especially since it is undocumented and unsupported, which means that MS won't test to see whether an update "breaks" this functionality.

    I see no benefit to using this method over the XML concatenation and lots of risk.  I simply don't think it's worth the risk.

    Drew

    Yes it's different, the problem with the quirky update is that it was updating a table on its primary key. This is just selecting the values with a select statement and concatenating them into a string variable in the order of the select, it's no different from the order they are in the select.
    I've found this link which says:
    If a SELECT statement returns more than one row and the variable references a non-scalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:
    USE AdventureWorks2014;
    GO
    DECLARE @EmpIDVariable int;

    SELECT @EmpIDVariable = BusinessEntityID
    FROM HumanResources.Employee
    ORDER BY BusinessEntityID DESC;

    SELECT @EmpIDVariable;
    GO

    This is saying that the ORDER BY does work.

    From SQL 2017 onward there is a better (documented) method to this using the STRING_AGG() function:
    DECLARE @Cols nvarchar(MAX)

    SELECT @Cols=STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION ASC)
      FROM INFORMATION_SCHEMA.COLUMNS c
     WHERE c.TABLE_NAME='MYTABLENAME'
       AND c.TABLE_SCHEMA='dbo'

    PRINT @Cols

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply