sql-serverssmssql-server-2014ssms-2014

Is there a way to select last N columns in SQL Server Results Tab?


Shortly,

I don't mean the last 5 rows. via SELECT TOP 5 * FROM Table Order By Key DESC

But I mean last 5 columns in Results Tab. enter image description here

Why? because each time I add a new column and filling it outside SQL-Server, I need to see its result without moving Horizontal Scroll. and many columns have the same name at the beginning.

It's not coding issues. but it's about the SQL-Server IDE Results tab itself.

I Searched many times. but this thing never asked I think. so please I want an approach for that. I assume something like built-in function or something anyone knows


Solution

  • Like I mentioned in the comments, if your table's definition is constantly changing this suggests a far larger design flaw in your database. Object definitions should be pretty static, and they should definitely not be changing every time you connect to the instance. That is the root cause of your problem, not that it's "too difficult", to type the name of 5 columns, rather than using *.

    This means fixing your design, which we can't comment on, but I suggest that is your next major step to do. Normalise your design, and use multiple tables (as I suspect you're adding extra columns each time due to a lack of normalisation).

    In the interim, you can use dynamic SQL:

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    
    DECLARE @Schema sysname = N'dbo',
            @Table sysname = N'rCTE_Vs_Tally';
    
    SET @SQL = N'SELECT ' +
               STUFF((SELECT TOP(5)
                             N',' + @CRLF + 
                             N'       ' + QUOTENAME(c.[name])
                      FROM sys.schemas s
                           JOIN sys.tables t ON s.schema_id = t.schema_id
                           JOIN sys.columns c ON t.object_id = c.object_id
                      WHERE s.[name] = @Schema
                        AND t.[name] = @Table
                      ORDER BY C.column_id DESC
                      FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,10,'') + @CRLF + 
              N'FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + @CRLF + 
              N'ORDER BY [Key] DESC;' --Assumes all tables have the column Key
    PRINT @SQL;
    
    
    EXEC sp_executesql @SQL;