Generating scripts in Management Studio

I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.

> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP
|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>
|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
>