copy structure of sqlserver table WITH index

Question

I need a script that runs several times per day to build tables (dozens of them) in a new database and copy certain information from an old database, which I will call the 'prev' database.

I want to copy the STRUCTURE of each of the tables from the 'prev' database to the new database. That's easy to do (thanks, google). I also want to be able to have the same information indexed in the new table. The indices are sometimes on columns and sometimes combinations of columns. I can build these indices manually easily enough - even though there's a lot of them. However, I'm wondering if there's an SQL scripting way of telling SQL, "Come on, man, look at the indices in that 'prev' table and put the same ones in this here new table."

-- rename underlying files of old db.
ALTER DATABASE abc SET OFFLINE
alter database abc
modify file (name=abc,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xyz.mdf')
GO
ALTER DATABASE abc
modify file (name=abc_log,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xyz_log.ldf')
GO

ALTER DATABASE abc SET ONLINE

-- give new logical name to the prev database
-- not sure this will work, but I think it will
USE master
GO
ALTER DATABASE abc MODIFY NAME = xyz
GO

-- make a new database
CREATE DATABASE abc
GO

-- note that prev db is xyz, new one is abc but has no tables yet
USE abc
GO

-- for each table, copy the structure into table of new db with same name
SELECT TOP 0 INTO dbo.atab FROM xyz.dbo.atab
GO
  -- for each index in THIS table
  CREATE INDEX idx_field1 ON db.atab  (field1)
  GO
  CREATE INDEX idx_combined ON db.atab  (field2, field3)
  GO
  -- etc.

Anyway, I think something like this will work. Is there a better way to do that last part?

Answer 1

You do not mention the great insight that google offered.

However, since you are using SQL Server, you should know about scripting. In SQL Server Management Studio, you can right click on the database and choose Tasks --> Generate Scripts .... This brings you to a wizard that will generate a script for some or all objects in the database, including indexes and constraints on the tables.