Feature Suggestion: Built in expand for replacing * with the column names

[jcouture100]

If you put the cursor after the asterisk (*) in a SQL statement, then pressing tab/enter/other will replace the * with the field list in the table.

[alex-bochkov]

This is tricky to implement properly because it requires understanding the underlying schema. That usually depends on executing the code, which in turn requires the code to already be valid - but it may still be in progress while you are writing it.

When two or more tables are involved, you also need to assign the correct table aliases. That requires schema knowledge as well, especially in cases involving temp tables.

As a partial workaround, I currently use the Copy column names command.

[jcouture100]

Hi @alex-bochkov,

I don't know if the SQL below will help you with this. The example SQL below retrieves the columns in Alpha order.
If you leave the ORDER BY clause out, it returns the field in the order that they appear in the table designer.

-- Identify the database
USE [ADVENTUREWORKS]
GO

-- Identify the table you want the column names for
DECLARE @tableName varchar(50) = 'PERSON';

-- Select the column names in alphabetical order
SELECT c.[COLUMN_NAME]
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @tableName
ORDER BY 1;   -- We can use the column number here since we only are returning the single column

[jcouture100]

Also, it appears that SSMS already knows what the fields are behind the asterisk. The tooltip on hovering over the asterisk displays the list of fields that under lie the value. I'll look around a bit to see if I can get to the source of the tooltip values.

[alex-bochkov]

Yeah, IntelliSense must be aware of all underlying fields... we just need to find a back door to it :)
I didn't even think of it...

[jcouture100]

Hi @alex-bochkov,

I did a bit more research on this. Looks like some of the add-ins are using the parser "ANTLR" (https://www.antlr.org/) to parse the SQL so it can get to the table names/field names. I looked in my SSMS extensions folder and the DevArt SQL Complete addin uses ANTLR.

Others recommend using the SQL Parsing library, Microsoft.SqlServer.TransactSql.ScriptDom.
Interestingly, I see DLLs for Antlr in Visual Studio, JetBrains, Devart dbForge SQL Complete, SSMS Copilot, etc. It seems to be a pretty popular library.

Source: https://community.webshinetech.com/t/c-technique-extracting-table-and-field-names-from-sql-query/1397

using Microsoft.SqlServer.TransactSql.ScriptDom;

var parser = new TSql150Parser(false);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(sqlQuery), out errors);

var visitor = new CustomSqlVisitor();
fragment.Accept(visitor);

// visitor.Tables and visitor.Fields would contain your extracted data

[alex-bochkov]

Hi John,
T-SQL parsers tokenize the existing query text and do not pull metadata directly from the database.
(the Format Query command in this extension is powered by Microsoft.SqlServer.TransactSql.ScriptDom)
I will take a closer look at this when I get a chance.