How can I stop SQL Server Management Studio replacing 'SELECT *' with the column list ?

Question

SQL Server Mgmt Studio is driving me crazy.

If I create a view and SELECT '*' from a table, it's all OK and I can save the view. Looking at the SQL for the view (eg.by scripting a CREATE) reveals that the 'SELECT *' really is saved to the view's SQL.

But as soon as I reopen the view using the GUI (right click > modify), SELECT * is replaced with a column list of all the columns in the table.

How can I stop Management Studio from doing this ? I want my 'SELECT *' to remain just that.

Perhaps it's just the difficulty of googling 'SELECT *' that prevented me from finding anything remotely relevant to this (i did put it in double quotes).

Please, I am highly experienced in Transact-SQL, so please DON'T give me a lecture on why I shouldn't be using SELECT *. I know all the pros and cons and I do use it at times. It's a language feature, and like all language features can be used for good or evil (I emphatically do NOT agree that it is never appropriate to use it).

Edit: I'm giving Marc the answer, since it seems it is not possible to turn this behaviour off. Problem is considered closed. I note that Enterprise Manager did no similar thing. The workaround is to either edit SQL as text, or go to a product other than Managment Studio. Or constantly edit out the column list and replace the * every time you edit a view. Sigh.

Answer 1

When SQL Server Mgmt Studio creates a view, I assume they're expanding the * to the complete list of columns that are present in the underlying table(s) at that particular time exactly for this reason: what if one of the underlying tables changes? Do you want the new columns to just simply show up in every view that references that table?? Seriously???

I think Microsoft tries to impmenent the "element of least surprise" here - your view will contain those columns that are present at the time the view gets created - and it stays that way, unless you explicitly and knowingly change that.

I for one wouldn't want to have my views suddenly having more columns than before, when an underlying table changes..... do you??

And I don't think there's any setting in Mgmt Studio to turn this behavior off, sorry.

Answer 2

Don't use the GUI editor.

Instead use the T-SQL editor. You get this by selecting "Script View As" -> "ALTER to" -> "New Query Window" from the right-click menu.

Answer 3

Try either of these: they are alternatives to using the GUI and can be setup as snippets with keyboard shortcuts:

select view_definition 
from information_schema.views
where table_name = 'viewname'

or

exec sp_helptext 'viewname'

The results will retain the "select *". (Tested)