How to stop SQL Server Management Studio by replacing "SELECT *" with a list of columns?

Question

SQL Server Mgmt Studio is driving me crazy.

If I create a view and SELECT '*' from the table, everything is OK and I can save the view. A look at the SQL for the view (for example, the CREATE script) shows that "SELECT *" is actually stored in the SQL view.

But as soon as I re-open the view using the GUI (right-click> edit), SELECT * will be replaced with a list of columns of all columns in the table.

How can I stop Management Studio from doing this? I want my SELECT to stay that way.

Perhaps this is just a googling 'SELECT *' difficulty that prevented me from finding something remotely relevant to this (I put it in double quotes).

Please, I am very experienced in Transact-SQL, so please do not give me lectures on why I should not use SELECT *. I know all the pros and cons, and I sometimes use it. This is a language function, and, like all language functions, it can be used for good or evil (I strongly disagree that I should never use it).

Edit: I give Mark the answer, as it seems that this behavior cannot be disabled. The problem is considered closed. I note that Enterprise Manager did nothing of the kind. The workaround is to either edit SQL as text, or switch to another product besides Managment Studio. Or, constantly edit the column list and replace * each time you edit the view. Sigh.

Answer 1

When SQL Server Mgmt Studio creates the view, I assume that it expands * to the full list of columns that are present in the base table (s) at this particular time for this very reason: what if one of the changes to the base tables? Do you want the new columns to simply appear in all views that reference this table? Jokes aside???

I think Microsoft is trying crazy about the “element of least surprise” here — your view will contain the columns that were present at the time the view was created — and it will remain that way unless you explicitly and knowingly change it.

I would not want my views to unexpectedly have more columns than before when the base table changes ..... are you?

And I do not think that Mgmt Studio has no settings to disable this behavior, sorry.

Answer 2

Do not use the GUI editor.

Use the T-SQL editor instead. You will get this by choosing "Script View As" → "ALTER to" → "New Query Window" in the right-click menu.

Answer 3

Try one of the following: they are an alternative to using the graphical user interface and can be configured as shortcut keys:

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

or

 exec sp_helptext 'viewname' 

Results will save "select *". (Tested)