0

I have an SQL 2008 R2 Server that is hosting multiple databases.

Is it possible to create a new database and create the tables based on the schema of an existing one?

For example database_1 contains approx 200 tables and full of data. I would like to create a new database called, say, tenant_1 that is based entirely on database_1 but without any data at all.

Both databases are on the same server and in the same SQL instance.

I also need to do all that via an SQL query without any interaction of the SQL Management Studio GUI.

Is that possible?

alcomcomputing
  • 307
  • 1
  • 4
  • 17
  • Please read [ask] and show what you have tried. Plenty of online resources explain how to query INFORMATION_SCHEMA and generate CREATE TABLE scripts from there. – CodeCaster Jan 22 '19 at 13:19
  • Thank you for the guidance, I have searched every article already before posting on StackOverflow and can't find an answer. To be clear, I'm not looking to create individual tables. I'm looking for a script that can easily create a new database from the schema of an existing one without resorting to GUI. – alcomcomputing Jan 22 '19 at 13:34

1 Answers1

2

In SQL Server under Databases, select the appropriate database. Right-click the database and select task -> generate scripts. From there you can take all table create and other scripts.

You can simply execute these in your other database...

Ali Fidanli
  • 1,342
  • 8
  • 12
  • Thanks Ali, but as I said I don't want to use the GUI so that doesn't work for us as we would have to do that every time we change the source database. What I'm looking for is a way to easily copy the most current database into a new database. Azure has a 'create database from' function which would be great but I wonder if there is a way to do this via SQL Server 2008 R2 without using the GUI at all. – alcomcomputing Jan 22 '19 at 13:41