SQL Server 2008 Clone table with indexes in .Net

Question

What would be the simplest way to create an exact structural replica for Table1 and all its indexes in the same database. Let's say Table1Dupl where all constraints/indexes from Table1 with suffix Dupl. I understand that I may do manual renaming "script table as " "create to" from Management Studio, but looking for fully programmatic way.

Answer 1

The programmatic way is to query for columns, indexes, foreign keys and triggers, build the scripts and execute it against the target database. For columns and foreign keys, you can look at the INFORMATION_SCHEMA views. For indexes investigate the sys.sysindexes and sys.index_columns catalog views. For triggers, investigate the sys.systriggers catalog view. Another means to help is to run SQL Server Profiler while you expand the list of indexes for a table or while you script an index to see what query Management Studio uses to get that information.

Answer 2

You could investigate SMO for this.

Edit but I see you've tagged the question "smo" so I guess you know this!?

Or if you want to avoid a dependency on that you could see Borgbeard's script here.

Answer 3

Use SMO to get a reference to your Table.
Once you have this reference, simply use ScriptOptions to specify what you would like to script.
Once scripted, you will have a StringCollection containing the text used, exactly as shown when you do a "Script Table As".
Since this is text in memory, you can do a simple search and replace.

    Microsoft.SqlServer.Management.Smo.Table oSmo;

    Microsoft.SqlServer.Management.Smo.ScriptingOptions oScriptingOptions 
        = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
    oScriptingOptions.Triggers = true;

    StringCollection myStrColl = new StringCollection();
    myStrColl = oSmo.Script(oScriptingOptions);