Is there a "poor man's" alternative to RedGate for scripting out entire database schema?

sql-server sql-server-2000 sqltools
Michael Lang picture Michael Lang · Oct 4, 2008 · Viewed 16.4k times · Source

I'm in a situation where I would to generate a script for a database that I could run on another server and get a database identical to the original one, but without any of the data. In essence, I want to end up with a big create script that captures the database schema.

I am working in an environment that has SQL Server 2000 installed, and I am unable to install the 2005 client tools (in the event that they would help). I can't afford RedGate, but I really would like to have a database with identical schema on another server.

Any suggestions? Any simple .exe (no installation required) tools, tips, or T-SQL tricks would be much appreciated.

Update: The database I'm working with has 200+ tables and several foreign-key relationships and constraints, so manually scripting each table and pasting together the script is not a viable option. I'm looking for something better than this manual solution

Additional Update Unless I'm completely missing something, this is not a viable solution using the SQL 2000 tools. When I select the option to generate a create script on a database. I end up with a script that contains a CREATE DATABASE command, and creates none of the objects - the tables, the constraints, etc. SQL 2005's Management studio may handle the objects as well, but the database is in an environment where there is no way for me to connect an installation of Management Studio to it.

Answer

BoltBait picture BoltBait · Oct 4, 2008

Run SQL Server Management Studio, right click on the database and select Script Database as > Create to > file

That's for SQL Server 2005. SQL Server 2000 Enterprise Manager has a similar command. Just right-click on the database > All Tasks > Generate Scripts.

EDIT: In SQL Server 2005, you can select "Database" in the object explorer pane and select several databases in the details pane. Then, right-click on your selection and "Script Database as > Create to > file". This will cause it to put them all into one script and it will include all tables, keys, stored procedures, and constraints.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More