Generate SQL Scripts

Hi all,
In Enterprise Manager, I could right click on a database and "generate
SQL Scripts" for hundreds of objects at once. In 2005, though, I've
only been able to generate scripts one at a time in Object Explorer,
and I haven't found any way to change the scripting option. Surely I'm
missing something?
- How do I generate scripts in 2005 (for many objects at a time, with
some custom options, as in SQL2000)?
- How do I change the Object Explorer right-click scripting options in
2005, when I'm scripting single objects?
Thanks in advance,
Myron> - How do I generate scripts in 2005 (for many objects at a time, with
> some custom options, as in SQL2000)?
Yes, seems like a step backward in this regard. Here are some excerpts from
a thread in MSDN's tools_general group that revolved around scripting all
the stored procedures (which could of course be replicated for several other
types of objects as well):
<I posted this>
Well, knowing that we can do this:
SELECT Object_Definition(Object_ID) FROM sys.procedures
The following might seem like a real brute force approach, but this will
generate the command line scripts necessary to do this (don't forget to use
the correct server and database, and change -E to -U/-P if using SQL auth):
select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_ID)
from sys.procedures
WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
Take the output, paste into a .bat file, and run. (You may wish to modify
the result first, e.g. leave out specific procedures.) This should make the
generation of the files pretty painless, though they will not include any of
the typical SET options ON at the top and OFF at the end.
Of course, you could take this further, like writing these commands directly
to a batch file by executing xp_cmdshell directly, and then even executing
the file when you're done. But I was too lazy to deal with the nesting
quotes, which would quickly get far beyond anything I'd have the patience
for right now. :-) I also didn't want to get into the process of enabling
xp_cmdshell, which is disabled by default, and for good reason. If you have
enabled xp_cmdshell, feel free to experiment.
<Nilton posted this>
Hi,
I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
line utility for it. It can be found in the C:\Program Files\Microsoft
SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
2000 CD. It appears to be missing in SQL Server 2005 but...
More info..
http://www.norbtechnologies.com/pdf...>
%20Script.pdf|||Actually, in Microsoft SQL Server Management Studio, if you open your
database in Object Explorer, then go to tasks --> generate scripts and follo
w
the wizard through each step, you can script out any part of your database.
I
use this very often and it works very well.
HTH, Todd
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, seems like a step backward in this regard. Here are some excerpts fr
om
> a thread in MSDN's tools_general group that revolved around scripting all
> the stored procedures (which could of course be replicated for several oth
er
> types of objects as well):
> <I posted this>
> Well, knowing that we can do this:
> SELECT Object_Definition(Object_ID) FROM sys.procedures
> The following might seem like a real brute force approach, but this will
> generate the command line scripts necessary to do this (don't forget to us
e
> the correct server and database, and change -E to -U/-P if using SQL auth)
:
> select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_I
D)
> from sys.procedures
> WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
> Take the output, paste into a .bat file, and run. (You may wish to modify
> the result first, e.g. leave out specific procedures.) This should make t
he
> generation of the files pretty painless, though they will not include any
of
> the typical SET options ON at the top and OFF at the end.
> Of course, you could take this further, like writing these commands direct
ly
> to a batch file by executing xp_cmdshell directly, and then even executing
> the file when you're done. But I was too lazy to deal with the nesting
> quotes, which would quickly get far beyond anything I'd have the patience
> for right now. :-) I also didn't want to get into the process of enablin
g
> xp_cmdshell, which is disabled by default, and for good reason. If you ha
ve
> enabled xp_cmdshell, feel free to experiment.
>
> <Nilton posted this>
> Hi,
> I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
> line utility for it. It can be found in the C:\Program Files\Microsoft
> SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
> 2000 CD. It appears to be missing in SQL Server 2005 but...
> More info..
> http://www.norbtechnologies.com/pdf...on%20Script.pdf
>
>|||Another alternative is selecting a group of elements in the Summary window
and selecting Script > ... from the context menu. E.g. when the Tables node
is selected in the Object Explorer, the Summary window contains the list of
all user tables - select one or more to create scripts.
ML|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||It's truly a step backwards. I'm writing code to do this now. It's only
about 30% complete but I'm getting tables, sps, views, triggers and
functions. Working on jobs right now. If you're interested I'll email you
the current code, unless you'd prefer to wait until its 100%.
It's a DLL and a front end console application with the following usage.
I'll add a GUI when I have time:
========================================
========================
DatabaseScriptor [-Out="{output folder}"][ -Preserve]
[ -Server="{name}"][ -Database="{name}"][ -User="" -Password=""]
[ -Include {objects}][ -Exclude {objects}][ -Encrypt][ -?]
[ -Force][ -NoDirs][-Quiet]
========================================
========================
This application is used to script objects in a named SQL Server database to
flat files.
SQL Server 2005's scripting functionality removed the ability to do this as
SQL Server 2000 did from the user interface.
========================================
========================
-? = Show this help text.
-D[atabase] = Optional. The name of the database whose object(s) will be
scripted. If not specified the user/login's default database
will be used.
-En[crypt] = Optional. If encryption is available, force encrypted
communication.
-Ex[clude] = Optional. The list of objects to exclude. If not specified no
objects will be exclueded.
-F[orce] = Optional. If true the output files will be overwritten
regardless of readonly state. Default is False.
-I[nclude] = Optional. The list of objects to script. If not specified all
objects will be included.
-N[oDirs] = Optional. If specified all files will be output to the specified
folder. Specialized folders will not be created.
-O[ut] = Optional. The folder to which to build the object tree. If not
specified all objects will be scripted to the console.
Duplicate file names will be overwritten.
-Pa[ssword] = Optional. The password for the SQL Server login.
-Q[uiet] = Optional. If specified no output will be sent to the console.
By default object names will be shown.
-Pr[eserve] = Optional. If true the output files will NOT be overwritten
regardless of readonly state. If false all files are overwritten.
Default is False.
-S[erver] = Optional. The name of the server that contains the database.
If not set, (local) will be assumed.
-U[ser] = Optional. The SQL Server login name to use for the scripting
operation. If not supplied the active user's account will be
used.
========================================
========================