CREATE SCHEMA COMMAND? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

CREATE SCHEMA COMMAND?

Author

Topic

REDTAIL_CODEGUY
Starting Member

17 Posts

Posted - 2011-08-22 : 13:36:04

I'm using SQL-server 2008R2 without tools. I have a third-party
query-analyzer with which I can execute sql queries.

Does the "Create Schema" command give you a schema that can
be used to re-build the database structure?

If so, can anyone give me an example that creates all the tables and everything that would be needed to duplicate the current database?

I want to set up a test-database that mirrors production. Without data is fine; just the structure...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-22 : 13:44:26

CREATE SCHEMA does not generate a script to build the schema. I do not know what your third party tool offers as far as generating scripts, but you can easily accomplish this task in Management Studio. In SSMS, you would right click the database, tasks, then generate scripts.

If you don't mind having the data, then I'd recommend backup/restore method.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

REDTAIL_CODEGUY
Starting Member

17 Posts

Posted - 2011-08-23 : 00:09:39

quote:
Originally posted by tkizer

CREATE SCHEMA does not generate a script to build the schema. I do not know what your third party tool offers as far as generating scripts, but you can easily accomplish this task in Management Studio. In SSMS, you would right click the database, tasks, then generate scripts.

If you don't mind having the data, then I'd recommend backup/restore method.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I finally realized that it would create an internal schema and not what I wanted. I don't think there is a third-party query-analyzer tool for mssql that will output a useable schema... So, I bit-the-bullet today and installed the management studio express product.
They have a stand-alone version now.

I found that the "Script As" option was the better one for me. However, the schema is really not directly useable. It's a complex database and the script needs all kinds of modifications. I'm working on it now... It doesn't build them in the proper order for certain constraints, etc...

I guess it was the best that I could do though, rather than typing things in manually ;).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources