title: copy structure of sqlserver table WITH index
body: <p>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.</p>
  
  <p>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."</p>
  
  <pre><code>-- 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.
  </code></pre>
  
  <p>Anyway, I think something like this will work.  Is there a better way to do that last part?</p>