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

Question

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?

So the desired results would be:

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

select 
 MyColumn1
,MyColumn2
,MyColumn3
from A

Answer 1

In SQL Server Management Studio, you can do what you want.

On the left side of the screen, expand the database to get the table name. Then, drag the table name over to the query pane and it will list all the columns.

Answer 2

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:

Click over "Options"

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:

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

Answer 3

Converting * in a cte to column names in Management studio

• create a table using ... select * into MyTable from A
• you can drag Object Explorer / Databases / MyDb / Tables / MyTable / Columns into your query
• or you can use Script table as Select if you prefer column names with []
• delete MyTable

Answer 4

If you are just trying to get the full list of columns for a table, (not CTE) you can do this:

In SSMS, open the Object Explorer and find the Table. Right click on it and choose "Select Top 1000 Rows".

It will give you a query like this:

SELECT TOP (1000) [ColumnName1]
    ,[ColumnName2]
    ,[Column Name 3]
    ...
FROM [databaseName].[schemaName].[TableName]

exhaustively listing all of the column names with square brackets around them.

Answer 5

It is called the wildcard and it is bad practice to use it in production code. It is also bad practice to do a "SELECT *" on anything without at least a "TOP n" expression.

For instance a view which includes a wildcard will have to be rebuilt in order to properly function again if you ever change the schema of the table it refers.

As Gordon says, you can use the UI or, as I prefer myself, intellisense the column names.

The UI will impose the use of the square bracket, which enhances compatibility, especially for objects named after reserved keywords or starting with a number, for instance.