Columns ordered differently from one environment to another

Question

Hello guys !

I wrote the following script to get the column names from a SQL table:

 DECLARE @SchemaName VARCHAR(20);  
 DECLARE @TableName VARCHAR(200);  
 DECLARE @Alias VARCHAR(20);   
 SET @SchemaName = 'dbo';  
 SET @TableName = 'tblSUFGenericAM202209';  
 SET @Alias = NULL;  
  
DECLARE @ColumnList VARCHAR(max);  
  
    IF EXISTS  
    (  
        SELECT 1  
        FROM information_schema.tables t  
        WHERE t.table_schema = @SchemaName  
              AND t.table_name = @TableName  
    )  
        BEGIN  
	SELECT @ColumnList = ISNULL(@ColumnList, ' ') + ',' +   
	   (CASE WHEN @Alias IS NULL THEN QUOTENAME(c.column_name)  
	    ELSE QUOTENAME(@Alias) + '.' + QUOTENAME(c.COLUMN_NAME) END)  
	    FROM Information_schema.columns c  
	    WHERE c.table_name = @TableName  
		   AND c.table_schema = @SchemaName  
		  
	    SELECT @ColumnList = SUBSTRING(LTRIM(@ColumnList),2,LEN(@columnlist)-1)  
  
        END;  
	ELSE  
	BEGIN  
	    SET @ColumnList = NULL;  
	END  
  
	PRINT @ColumnList;  

It does what it's suppose to. However, I just realized that the order of the columns is not the same when I run the script on our Dev and Test environments.

DEV :
[DivisionAcronymEnglish],[DivisionAcronymFrench],[OperatingEntityNumber],[MeasureEnglish],[MeasureFrench],[SizeRaw],[SizeConverted],[UnitOfMeasureEnglish],[UnitOfMeasureFrench],[DefaultUnitOfMeasureEnglish],[DefaultUnitOfMeasureFrench],[VintageDate],[ActivationDate],[CollectEnglish],[CollectFrench],[LastUpdateDate],[Userid],[AdditionalMeasuresCharacteristicsId]

TEST :
[ActivationDate],[AdditionalMeasuresCharacteristicsId],[CollectEnglish],[CollectFrench],[DefaultUnitOfMeasureEnglish],[DefaultUnitOfMeasureFrench],[DivisionAcronymEnglish],[DivisionAcronymFrench],[LastUpdateDate],[MeasureEnglish],[MeasureFrench],[OperatingEntityNumber],[SizeConverted],[SizeRaw],[UnitOfMeasureEnglish],[UnitOfMeasureFrench],[Userid],[VintageDate]

I was expecting the column order would be the same as the input table (DEV). Can somebody tell me why the result is not the same and how I can fix this ? Some of the tables that I need column names from contains tones of them !!

Thanks in advance for your help ;-)

Mylene

Answer 1

When you need to have an order list from SELECT, you need to use ORDER BY clause,
If your table is the same for all environments, you can add this line with column ORDINAL_POSITION in the following section:

SELECT @ColumnList = ISNULL(@ColumnList, ' ') + ',' +   
    (CASE WHEN @Alias IS NULL THEN QUOTENAME(c.column_name)  
     ELSE QUOTENAME(@Alias) + '.' + QUOTENAME(c.COLUMN_NAME) END)  
     FROM Information_schema.columns c  
     WHERE c.table_name = @TableName  
        AND c.table_schema = @SchemaName  

Order by ORDINAL_POSITION

Answer 2

If you need an order list in a SELECT query, you must have an ORDER BY clause to guarantee your resultset ( one row result is an exception but does not hurt) .

Answer 3

As simple as that !! Thank you guys !