In Enterprise Manager it was possible to generate a script file per object,
I do not see this option in Management Studio. Is it no longer available? If
it is, where do I find this option? If it isn't, is there any other way to
do this?

TIA

Michael MacGregor
Database Architect

Right click on the object you want to script and its there.

If you want to script both the schema and data of a table then you can
use xSQL Software's xSQL Data Compare - there too you right click on
the object and script it.

JC
xSQL Software
http://www.xsqlsoftware.com

What I want to be able to do is to select the database and generate scripts
for each and every object in the database, one script per object. This was
possible in Enterprise Manager but not in Management Studio.

Due to time constraints, a third party software is not suitable.

Thanks.

MTM

One option for you that doesn't involve installing SP2 would be to use
this console app: http://www.codeplex.com/scriptdb

it will generate a separate file for each object, for any 2000 or 2005
database.

Show quoteHide quote

Interesting little utility. Thanks Jesse.

MTM

Doesn't quite do what I want but thanks anyway.

MTM

Hi Micheal,

Michael MacGregor wrote:
right click the database, select "Tasks" from the context menu,
select "Generate scripts..." and there you are :-)

regards,
Gerald

Gerald,

That doesn't work, not for the version of SQL 2005 that I have, otherwise I
wouldn't have posted the question.

Michael MacGregor
Database Architect

Michael MacGregor (nospam@nospam.com) writes:
The option was not available in the RTM version of Mgmt Studio, but
it was added in SP2.

So I'd have to install SP2 to get this functionality? Are there any
drawbacks to installing SP2?

MTM

Michael MacGregor (nospam@nospam.com) writes:
There are two serious bugs with maintenance plans in SP2, so if you
use maintenance plans, make sure to also install a cumultative update.
There are more then one, as MS spits one every two months. Since a CU
is not as well tested as a service pack, it's difficult to say "just
take the latest". But it's not directly trivial to find the right CU
which only has the maintenance-plans fixes.

Michael,

If you can't load Sp2 on the server you may want to just load it on your
client tools assuming they are on a different machine. That would probably
not be a supported configuration but may get you what you need in the short
term.

Show quoteHide quote

Hmmmm. Now there's an idea.

MTM

Tried that, and it didn't complain in the slightest with the use of
Management Studio at SP2 connecting to SQL 2005 RTM. Unfortunately the
options available don't quite match with what I'm trying to do.

I would like to generate a script file per table and to have that script
include both PK and indexes, but not FKs which I would like in a separate
script, one file per table.

Thanks anyway for the suggestion, at least now I get to evaluate SP2.

Michael MacGregor
Database Architect

Michael MacGregor (nospam@nospam.com) writes:
Was it really possible to script FKs separately with Enterprise Manager?
I can't seem to find any such option. And in case, this requirement
was not in your original post.

The best I can offer is to run the script wizard twice. First include
all but FKs, and the second time include only FKs. Then write a program
that removes the CREATE TABLE statements from those files. Or extracts
the ALTER TABLE statements.

I did not realize that is what you wanted. From your original post it
sounded like you wanted each table and their associated objects to be in a
different file. I did not know you could separate the FK's like that in
2000. In all the years I have used SQL Server I don't think I have ever
scripted to individual files.

Show quoteHide quote

Just thought I would add that the utility I posted (scriptdb) earlier
on in this thread *does* create a seperate file for each FK by
default, so if you can bear to break away from SSMS, it will satisfy
your requirement in that area.

OTOH it also generates a separate file for indexes and PKs too so
you'd have some trivial edits to do to get the behavior you want.  :)

Show quoteHide quote

Jesse,

Excellent idea, thanks.

Michael MacGregor
Database Architect

Well maybe not quite. Ideally I'd like one file per table with all the FKs
for that table, but obviously I will have to live with some file editing for
the moment. Unless there is another utility out there that will do exactly
what I want. Haven't found one yet though.

Michael MacGregor
Database Architect

you may want to write your own utility using SMO

This link will get you started:
http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

there is the source code for the utility described in previous article
http://www.sqlteam.com/downloads/scriptio.0.5.1.0.zip

I've written an utility to make atomated daily check-ins of all db changes
in VSS.

Ulrich


Show quoteHide quote

Nice idea but I kinda need something now and for me to figure out how to
code such a utility, well I haven't done any coding outside of SQL for a
very long time so I doubt if I'm going to do that.

MTM

Andrew and Erland,

My apologies about not having initially mentioned that I wanted separate
files for the CREATE TABLE and for the FKs for each table, but the immediate
problem was that I couldn't get SSMS to generate a file per object, and it
wasn't until I got that to work by installing SP2 that I discovered I
couldn't do exactly what I used to be able to do with EM.

By way of explanation, we use VSS and originally the files for tables were
actually generated by Visual Studio and broken down into at least four
different files, .tab, .kci, .fky & .ext. We are about to upgradet to VSS
2005 and one developer was exploring some options and found that it now
generates .sql files. This is my preference anyway as I not familiar with
Visual Studio and have always created script files for the CREATE TABLE
incorporating the PK and another file for the FKs, one file per table. It
does not appear as if Visual Studio can actually do this for us so I was
looking at generating the scripts from SSMS as I know I could do something
very close to what I wanted with EM (actually even EM doesn't quite do what
we need either, it will not separate the PKs and FKs).

At this point in time I think I will end up using a combination of SSMS, to
generate the CREATE TABLE scripts, and the ScriptDB utility suggested by
Jesse, to generate the FK scripts.

Thanks for your help and suggestions.

Michael MacGregor
Database Architect

With Enterprise Manager you could deselect the object CREATE option but keep
the PK & FK option so that the resulting file(s) only had the PK and FK
statements. In Management Studio when I attempt to do the same thing, no
code is generated at all.

Very frustrating.

Michael MacGregor
Database Architect

Backup job failed-SVP2
Profiler temp file
debugger problems in query analyzer
ODBC DSN for SQL Server via script
sbd to mdf