sqlsql-serversql-server-2008sql-server-2017sql-server-2019

Edit asterisk symbol into list of column names in SSMS by wildcard expansion


How to change * [asterisk symbol] into list of column names? I can view the list of column names after placing mouse cursor over the *. Is it possible to click-crack on something to change the * into names without running the script an inserting the results into some dbo.temp table?

enter image description here

So the desired results would be:

with A as (select 
 MyColumn1=1 
,MyColumn2=2 
,MyColumn3=3)

select 
 MyColumn1
,MyColumn2
,MyColumn3
from A

Solution

  • There is an option into Sql Server Management in which you can execute a stored procedure with the keyboard, you can configure that option to execute a procedure that lists the columns of a table, this is the way you can do it:

    enter image description here

    Click over "Options"

    enter image description here

    As you can see there are many keyboard shortcuts to execute a stored procedure, eg when you highlight a name of a table with the shortcut alt+f1 you can see the metadata of the table, I wrote a stored procedure that shows the lists of the columns of a table separated with ",", this is the procedure:

        Create Procedure [dbo].[NS_rs_columnas]
            @Tabla Sysname,
            @Alias Char(3)=null
        AS
        Begin
            Declare @Colums Nvarchar(Max)='';
    
            Select 
            @Colums+=','+isnull(Ltrim(Rtrim(@Alias))+'.','')+'['+b.name+']' + CHAR(13)+CHAR(10)
            from sys.tables a
            Inner join sys.all_columns b 
            on (a.object_id=b.object_id)
            Where a.name=ltrim(rtrim(@Tabla));
    
            Select ' '+Substring(@Colums,2,len(@Colums)-2);
        End
    

    So what you can do is configure a shortcut to execute that procedure.

    this is the result when I press the shortcut ctrl+f1 over a table name:

    enter image description here

    As you can see the procedure has two parameters, the second parameter is to send an alias, this is an example:

    enter image description here