Code to generate SQL Server table schema from ADO.NET

Question

I'd quite like to use ADO.NET to generate a CREATE TABLE script to create an exact copy of a given table.

The reason for this is persistence testing. I would like to know whether my application will persist to a particular database. I would like to be able to point the app to the database and table in question, and then the app will generate a new database with an exact copy of the specified table. Thus, persistence testing can take place against the cloned table without touching the original database, and when I'm done the new database can simply be dropped.

Before I embark on this ambitious project, I would like to know if anything already exists. I've tried Google, but all I can find are ways to get schema generation SQL through the SSMS UI, not through code.

D'Arcy Rittich · Accepted Answer · 2010-09-03 11:21:47Z

3

You can use SQL Management Objects (SMO) for this.

Example (C#)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Server srv = new Server(@".\SQLEXPRESS");
            Database db = srv.Databases["MyDB"];

            Scripter scrp = new Scripter(srv);
            scrp.Options.ScriptDrops = false;
            scrp.Options.WithDependencies = true;

            //Iterate through the tables in database and script each one. Display the script. 
            //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
            Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1];
            foreach (Table tb in db.Tables)
            {
                smoObjects[0] = tb.Urn;
                if (tb.IsSystemObject == false)
                {
                    System.Collections.Specialized.StringCollection sc;
                    sc = scrp.Script(smoObjects);
                    foreach (string st in sc)
                        Console.WriteLine(st);
                }
            }
            Console.ReadKey();
        }
    }
}

edited Sep 3, 2010 at 11:21

answered Sep 3, 2010 at 11:07

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

This is awesome. You've saved me a stack of time. Thank you!

Nathan Tregillus Over a year ago

epic. I was needing the dynamically create these things, this will save me a TON of time. thank you very much!

Collectives™ on Stack Overflow

Code to generate SQL Server table schema from ADO.NET

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related