Sql-server – “poor man’s” alternative to RedGate for scripting out entire database schema?

sql serversql-server-2000sqltools

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.

Best Answer

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.