Anexinet BI Blog: Expand Wildcards in SSDT

Developers are often under the gun to push out code as quickly as possible. And, whether we like it or not, speed kills. It kills our best intentions to please the client and kills our standards of development.

When the trail becomes worn and we can more easily avoid mistakes and sloppy code, even at a rapid pace, speed makes the difference between junior and senior developers. But it is not as simple as “don’t make mistakes” or “type faster,” this is more about knowing the way and not stumbling through trial and error. Sometimes it is just knowing the tricks. This post is about a new one I learned, today.

In SSDT, I usually turn on code review, which will surface up common code problems such as this one:

Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes. This error surfaced because there was a view in the project that was written like this:

CREATE VIEW [dbo].[SelectStar]

AS SELECT * FROM [dbo].[MyTableHasTooManyColumns]

Granted, this is actually the default code when you create a new view. So the rules engine says that the statement will change if the underlying table structure changes. While this is true, it is not actually the problem, itself. The problem is that consuming applications could inadvertently break because you are not controlling the structure of the data through the view. Views can be used to shield applications from changes to the database so that applications do not need to change at the whim of the database developer.

In SSDT, this can easily be remedied by right clicking on the “*” and selecting Refactor > Expand Wildcards….

This will bring up a dialogue which will show all of the columns in the table.

When you hit apply, all of the columns will be written out to the script and, with a little regex, it can be formatted properly too.