Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.

Is there a way to automate this script generation and drop the scripts to a temp location, rather than manually right clicking each object and generating them?

share|improve this question
    
I go for the simple route: petri.co.il/scripting-database-objects-in-powershell.htm –  Shawn Melton May 2 '13 at 2:46
add comment

2 Answers 2

SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts.

    public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
    {
        connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
        Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
        Database db = server.Databases[pDatabaseName];
        Table t = db.Tables[pTableName, pSchemaName];
        ScriptingOptions baseOptions = new ScriptingOptions();
        baseOptions.NoCollation = true;
        baseOptions.SchemaQualify = true;
        baseOptions.DriDefaults = true;
        baseOptions.IncludeHeaders = false;
        baseOptions.DriPrimaryKey = true;
        baseOptions.ExtendedProperties = true;
        //baseOptions.DriAll = true;
        //baseOptions.Indexes = true;
        //baseOptions.DriAllKeys = true;
        //baseOptions.SchemaQualifyForeignKeysReferences = true;
        baseOptions.EnforceScriptingOptions = true;
        StringCollection result = t.Script(baseOptions);
        return StringCreate(result);
    }
share|improve this answer
add comment

You can use Powershell and SMO to do this - simple, efficient and customizable. Such script to script out database objects can be found here and here

share|improve this answer
    
Thanks Kin! The first link did the trick. Is there a way we can automate and rename the table names in the output file. Lets say, the script generate create index commands for Table_A, I need to the keyword Table_A within the output file to Table_B, so that i can run the create scripts against table_B –  Amam May 2 '13 at 17:56
    
You can do a search+replace or use RegEx or generate the scripts with Powershell but just append _New to the script. Many ways to do it. –  Kin May 2 '13 at 18:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.