Generate Scripts: change “Convert UDDTs to Base Types” to True, if you don’t like procedure header c

If you have UDDTs in your database project and want to script them you should be able to do that using the wizard in management studio for scripting objects.

You find that wizard by right clicking on the database, where you have objects to script, and chose “Tasks”, “Generate Scripts…”. On the third step: “Set Scripting Options” there is a “advanced” button where various scripting options are set to fine-tune what and how you want your objects scripted. Books online are not so clear what you get if you change the default script option “Convert UDDTs to Base Types” from the default ‘False’ to ‘True’, though they have tried to describe that. “Convert UDDTs to Base Types When True, user-defined data types are converted into the base data types that under are used to create the user-defined data type. Use True when the user-defined data type does not exist in the database where the script will be run. Default is False, which indicates that user-defined data types will be scripted using the UDDT. ” That first sentence is just not correct in my opinion, lost me somewhere at the passage “that under are used to create”. Could be that I’m not natively english spoken. But if you have UDDTs and also have adopted this certain way to comment stored procedures with a /* comment /* style in the header of the procedure, like this example:

CREATE PROCEDURE [dbo].[doImportantStuff_SP] @my_ID [int] = 0 /********************************************************************** Description : Procedure does some important stuff Comment : Example : ***********************************************************************Created By : Developers Name Date : 1994-01-02 *********************************************************************** Revised By : AnotherDevs Name Date : 2011-02-16 Comment : Needed a change in some behaviour ***********************************************************************/ AS ...

I get an unwanted behaviour if I have set the script option “Convert UDDTs to Base Types” to ‘True’. My comments will not be scripted! Instead I get this skewed header, except for having my comments removed, I also get the ‘WITH EXECUTE AS CALLER’ statement. CREATE PROCEDURE [dbo].[doImportantStuff_SP] @my_ID [int] = 0 WITH EXECUTE AS CALLER AS ... Took me some time to realize that this was why the comments didn’t show up anymore. Going through the options in the “most likely order to affect comments” didn’t lead me to this particular option, until one of the last ones tried – kind of “Murphys law” applied on that one. Not sure if I should call this a feature, bad luck of needing both the setting and comments, or if it’s actually a bug. 🙂

It depends on how the tools intended to act. Since the text about what the option does has been this way since 2005, and the Denali version is the same, I guess I need to generate two scripts and merge them myself, if I want both behaviours in one script. As a consultant in SQL Server I want to be as effective as possible when doing administrative tasks, so I get more time to think of solutions and write code. Other than that – the scripting tool is excellent in all it’s beauty.