exporting db schema

Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com

exporting db schema

Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.
JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com

exporting db schema

Using SQL Server Enterprise Manager,
how do I export a database schema?
Thanks.JoeDz wrote:
> Using SQL Server Enterprise Manager,
> how do I export a database schema?
> Thanks.
Right Click Database - All Tasks - Generate SQL Scripts - Click Show
All - Click Script All Objects (or the ones you want) - Under Formatting
Tab check CREATE / DROP / and possibly Dependent Objects if you're not
selecting everything - Under Options Tab click Script Indexes / Triggers
/ Database (if needed) / users (if needed), etc.
Select file format ANSI or UNICODE
Click OK.
David Gugick
Imceda Software
www.imceda.com

Exporting Database Schema

Is there a way for VS2005 to export the database schema of a .mdf into a .sql file? Preferably from a Web PRoject, not a Database Project, but right now I am anything but picky, so whatever works.

Thanks.

It is not export because with a .sql file you are just generating a create database statement, you right click on your database in Management Studio and click on create to generate the create database statement. This is the way to do it because VS is to create DML(data manipulation language) like queries but create database is different it is DDL(data definition language). Hope this helps.|||

You should be able to do this easily in Management Studio: right click a database->Tasks->Generate Scripts->Finish the wizard.

|||It is the SLQ Managment Tool you two are refering to, right? Does that mean that I need the Express version, or is it somewhere I can't find it as I am using the full VS2005 package?|||

You can download the Management Studio from this link:

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Yeah, I know, I was just double checking. Thanks.

One follow up question though. I have it installed, but it can see only the .mdf files that are within the database project files, is there a way to make it see the ones I have in my Web Projects?

|||Right click "Database" and choose "Attach". Pick your .mdf, and then you can see it. Just make sure to unattach when you are done, or your web app won't be able to access it.sql

Export the schema and data

How do I export the database schema along with data, SP, Views..etc into a
script file ?Use DTS (for SQL 2000) or SSIS (for SQL 2005)
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:u1Z8ZY$qGHA.1368@.TK2MSFTNGP05.phx.gbl...
> How do I export the database schema along with data, SP, Views..etc into a
> script file ?
>|||Is DTS only apply to a single database object?
I want to export the whole database into a single script file.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uRajXfBrGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Use DTS (for SQL 2000) or SSIS (for SQL 2005)
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:u1Z8ZY$qGHA.1368@.TK2MSFTNGP05.phx.gbl...
>> How do I export the database schema along with data, SP, Views..etc into
>> a script file ?
>|||In Enterprise Manager, [Right Click] on the database, and select [All Tasks]
and then [Generate SQL Script].
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:OPlM5fV4GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Is DTS only apply to a single database object?
> I want to export the whole database into a single script file.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uRajXfBrGHA.2180@.TK2MSFTNGP05.phx.gbl...
>> Use DTS (for SQL 2000) or SSIS (for SQL 2005)
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
>> news:u1Z8ZY$qGHA.1368@.TK2MSFTNGP05.phx.gbl...
>> How do I export the database schema along with data, SP, Views..etc into
>> a script file ?
>>
>|||Arnie Rowland wrote:
> In Enterprise Manager, [Right Click] on the database, and select [All Tasks]
> and then [Generate SQL Script].
>
That won't include the data, however. By far, the easiest way to "get"
the schema AND data together is via a backup/restore. If you really
want a script, i.e. a bunch of INSERT statements, to reproduce a table,
have a look here:
http://www.realsqlguy.com/serendipity/archives/15-Table-For-Two.html
This is a rough script that needs cleaned up a bit, but it's usable as-is.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Export the schema and data

How do I export the database schema along with data, SP, Views..etc into a
script file ?Use DTS (for SQL 2000) or SSIS (for SQL 2005)
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:u1Z8ZY$qGHA.1368@.TK2MSFTNGP05.phx.gbl...
> How do I export the database schema along with data, SP, Views..etc into a
> script file ?
>|||Is DTS only apply to a single database object?
I want to export the whole database into a single script file.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uRajXfBrGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Use DTS (for SQL 2000) or SSIS (for SQL 2005)
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:u1Z8ZY$qGHA.1368@.TK2MSFTNGP05.phx.gbl...
>|||In Enterprise Manager, [Right Click] on the database, and select [Al
l Tasks]
and then [Generate SQL Script].
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:OPlM5fV4GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Is DTS only apply to a single database object?
> I want to export the whole database into a single script file.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uRajXfBrGHA.2180@.TK2MSFTNGP05.phx.gbl...
>|||Arnie Rowland wrote:
> In Enterprise Manager, [Right Click] on the database, and select [
All Tasks]
> and then [Generate SQL Script].
>
That won't include the data, however. By far, the easiest way to "get"
the schema AND data together is via a backup/restore. If you really
want a script, i.e. a bunch of INSERT statements, to reproduce a table,
have a look here:
http://www.realsqlguy.com/serendipi...le-For-Two.html
This is a rough script that needs cleaned up a bit, but it's usable as-is.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Export schema/DDL

Hey folks,

I am looking for a way to export the DDL for table objects from MS SQL 2K. In EM you can right click a table, select "All Tasks" then "Generate SQL". When you preview the script, you can copy it to the text editor of your choice and save it. I'd like to find a way to do this programatically - either through TSQL commands or DTS would be best. I'm OK to do VBScript w/in DTS but I'm not up to writing a full blown VB app.

I seem to remember in my Sybase days that there was a utility called "defncopy" that could be used to extract DDL but there seems to be no analogue in MS SQL 2K.

The purpose of all this is to extract the data and schema of a table in ASCII text so that we can zip it and burn to CD for archiving purposes.

Any help appreciated!You need version control if your db is in such a state of flux...

but that's just my own opinion (MOO)|||Originally posted by Brett Kaiser
You need version control if your db is in such a state of flux...

but that's just my own opinion (MOO)

Brett - nothing here to indicate "a state of flux" simply we need to be able to extract and archive the structure and data within these tables. We are archiving data that has gone stale and attempting to reduce the utilization on our hard drives. Unfortunately the restrictions of our industry require us to keep the data for a long period of time even if it is not "live".

Stick to the technical question at hand, MOO elsewhere please.|||Follow the Nigel's script from this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32519 to create the script.|||Originally posted by Satya
Follow the Nigel's script from this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32519 to create the script.

Thank you this seems like it will do the trick or serve as a basis for moddification.

Export Schema in SqlServer 2005

Hi all,

sorry for the rather trivial question but I couldn't figure this out by
myself.

How do you export a db schema either from the commandline or from the
GUI (if I remember correctly it's called Management Studio in the new
version).

Thanks in advance,
LorenzoIn Enterprise Manager:
1) open the databases folder
2) highlight the database you want to generate a SQL script for
3) right-click and select "All Tasks..."
4) select "Generate SQL Script"
the "Generate SQL Script" window pops-up.

At this point, I usually just click on the "select all" button near the
top-right corner. You can select objects individually for scripting at
the bottom-left sectio of the window or, at the top-left section, you
can select "all tables", etc.

hth,

victor dileo|||vjdileo wrote:
> In Enterprise Manager:
> 1) open the databases folder
> 2) highlight the database you want to generate a SQL script for
> 3) right-click and select "All Tasks..."
> 4) select "Generate SQL Script"
> the "Generate SQL Script" window pops-up.

Hi Victor,

thanks for your reply... guess that All Tasks didn't say anything good
to me! ;)

Lorenzo

Export Schema and Data

This is a totally newbie question, but...

I've create a database, I'm able to script the schema to a query window, file, etc.

I can't for the life of me find out how to export my data so that it is scripted into insert statements. The data is standardized lists of data I will be distributing with the DB.

For those of you familiar with mySQL, this would be the output of the mysqldump command which dumps schema and data all into one file

mysqldumb <db> -u user -p > mydatafile.txt

thanks,

-David

You won't be able to export it to INSERT statements, but it can be exported to a flatfile with column and row delimiters. Please see Books Online topic bcp utility.

|||

Greg-

Ok, I'm familiar with BCP, was just trying to figure out a better way. I'll use BCP and either DTS or write a perl script to parse my data into scripted insert statements.

Thanks,

-David

|||

Well, you can always use TSQL to generate it for you as well, and save the results as a file. This can be expensive if your table is huge. Here's one example:

select 'INSERT into dbo.t1 (col1, col2, col3) values (''' + col1 + ''', ''' + col2 + ''', ' + cast(col3 as varchar(10)) + ')'

from dbo.t1

Export Schema

Hi,

Not sure if this is a simple question...

I would like to export the schema of my database to allow me to print it out in an easily viewable form ( preferably in to visio ). Is this possible, perhaps through a wizard so i can just show the primary / secondary keys of the tables and how they all link ?

Many thanks for your responses.

in Visio there is an option to Update Model from Database. Open a visio window. You will have "Database " Menu . Select Update... Follow the on screen instruction(you will have to make connection and all.... i assume that u r familier with Visio) and one window you will have an option called update Model from database. Select that and go ahead. It will create schem of your database in visio

Madhu