Where do I set the default Script Formatting options for generating the TSQL to create database objects? I know how to "right-click > Script As > Create To..."
What I need is to set the default scripting behavior to include a check for existence, drop the object if it exists, and also include object-level permissions. In SQL 2000 Query Analyzer, it was easy (Tools > Options > Script ). The result would look like:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[CustOrderHist]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustOrderHist]
GO
CREATE PROCEDURE CustOrderHist @.CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @.CustomerID
AND C.CustomerID = O.CustomerID
AND O.OrderID = OD.OrderID
AND OD.ProductID = P.ProductID
GROUP BY ProductName
GO
GRANTEXECUTEON [dbo].[CustOrderHist]TO [public]
GO
But in SQL 2005 Management Studio, I can't find the options to enable these features. The result of a "script procedure to new window" only has the CREATE statement, and I hate hand-coding the check for existence and permissions. The snippets highlighted in yellow above simply aren't included in the generated script. ?!? 
Losing this capability will definitely be a huge issue in my shop, where we have about 15 devs and a couple of DBAs who are *very* accustomed to utilizing these "old features"...
Has anyone figured out how to get these back?
If you navigate through rightclick database > All Tasks > Generate Script you will have the full featured settings pane for tweaking your creation.
HTH, Jens K. Suessmeyer.http://www.sqlserver2005.de
|||
We've added a dialog to configure scripting option in Service Pack 2. It will be accessed from the Tools > Options top-level menu item, then selecting the new "scripting" options page.
Thanks,
Steve
|||
Thanks, Jens.
Unfortunately, this is not going to help much. What we need is the ability to create "sticky" settings for the defaults, which would be used by the Right Click (the object) > Script To > (file, new window, or clipboard)... and use our preferred settings to generate the DDL with the desired formatting.
1. There is no apparent way to set the defaults to my custom settings, I have to go through the "wizard" every time and reset the options the way I need them. That takes about 20 mouse clicks. We need to be able to configure the defaults and make them sticky, like having them saved under the user profile, and recalled the next time without intervention.
2. The wizard really doesn't get us past the problem of first manually creating the DROP statements and then manually creating the CREATE statements. We'd have to run the wizard twice to get both sets of statements:
Under "General > Script Behavior", there are options to:
- Generate CREATE statements only
- Generate DROP statements only
... but there is no option to Generate DROP statements followed by CREATE statements, even though the tool indicates there *ought to be* such an option. Is this the result of some dependant option selection that I did or did not make?
3. The wizard does not appear to have any option for generating individual script files for each object. You can either create a new single file containing all the objects you've selected, OR append the current operation's script output to an existing file. We need separate files for each object. Not one enormous script for everything.
4. The wizard's check for object-level permissions can be too granular. I scripted a view, and was surprised by the outcome! It might be handy if you actually *want* to script out every permission on every column for every user and role, but most of the time we grant permissions to a role on the entire view or table, not every individual column. And we tend to combine the permissions granted onto a single statement per role, like this:
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[theView] TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] TO [theROLE_READONLY]
GO
This was the output from the wizzie:
GRANT SELECT ON [dbo].[theView] TO [theROLE]
GO
GRANT INSERT ON [dbo].[theView] TO [theROLE]
GO
GRANT DELETE ON [dbo].[theView] TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([idn]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([idn]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([idn]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([fk_idn]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([fk_idn]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([fk_idn]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([cre_usr_acct]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([cre_usr_acct]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([cre_usr_acct]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([lvl_idn]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([lvl_idn]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([lvl_idn]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([lst_mod_usr_acct]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([lst_mod_usr_acct]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([lst_mod_usr_acct]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([plnt_cde]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([plnt_cde]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([plnt_cde]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([ste_cde]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([ste_cde]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([ste_cde]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([ship_dte]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([ship_dte]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([ship_dte]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([orig_usr_acct]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([orig_usr_acct]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([orig_usr_acct]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([orig_print_dte]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([orig_print_dte]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([orig_print_dte]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([lst_print_usr]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([lst_print_usr]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([lst_print_usr]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([lst_print_dte]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([lst_print_dte]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([lst_print_dte]) TO [theROLE_READONLY]
GO
GRANT SELECT ON [dbo].[theView] ([cmnt]) TO [theROLE]
GO
GRANT UPDATE ON [dbo].[theView] ([cmnt]) TO [theROLE]
GO
GRANT SELECT ON [dbo].[theView] ([cmnt]) TO [theROLE_READONLY]
Do I have to write some kind of SQL Management Studio Add-In to get where I want to go? Or is there a registry hack or config file I can change (documented or not) to get closer to the desired end state?
Regards,
Rich
|||
Thanks, Steve. This is good news! When does SP2 come out? Will it be all-inclusive or additive with SP1? We haven't yet installed SP1.
|||Maybe this little tool will help in the meantime. I just commented out what I don't want and use a batch file to start the app.
http://www.elsasoft.org/tools.htm
