Script Table With Data from code

Question

In SQL Server 2008 R2, a user can script out a table with the data by right clicking on the database, selecting Tasks and Generate Scripts. A wizard will pop up, asking users what they want (multiple tables, one table, etc) along with some advanced options (that allow the user to obtain both the table schema and data); for questions, see this useful post of what's being done (under "The Handy").

Unfortunately, SQL Server doesn't have a script to generate these scripts, and on occasion we have to script out some tables with all the data in them. Instead of manually doing this each time, I thought it would be easier to have a C# application call these processes for a test table and generate a script for the table with all the data, saving it as a local file.

When I use the SqlConnection, I don't seen an option to do this type of task (though it might be with something else). How is this wizard accessible?

Update: After the initial answer, I assumed this wasn't possible, so I worked out an interesting solution, for those more familiar with TSQL. I created a stored procedure similar to the one below:

CREATE PROCEDURE usp_ScriptTableAndData
AS
BEGIN

    CREATE TABLE ScriptTemp(
        Value VARCHAR(8000)
    )

    INSERT INTO ScriptTemp
    SELECT '/*   Build the Reference Table   */'

    INSERT INTO ScriptTemp
    SELECT 'USE [DB]
        GO

        SET ANSI_NULLS ON
        GO

        SET QUOTED_IDENTIFIER ON
        GO

        SET ANSI_PADDING ON
        GO

        CREATE TABLE [dbo].[Table](
            [ID] [int] IDENTITY(1,1) NOT NULL,
            [Reference] [varchar](50) NULL,
            [TableDate] [datetime] NOT NULL,
            [Display] [bit] NULL
        )

        SET ANSI_PADDING OFF
        GO


        INSERT INTO ScriptTemp
        SELECT '/*   Insert the Table Values    */'

        INSERT INTO ScriptTemp
        SELECT 'INSERT INTO Table VALUES(' + CAST(ID AS VARCHAR(3)) + 
            ',' + '''' + LTRIM(RTRIM(ISNULL(Reference,'NULL'))) + '''' + 
            ',' + '''' + CONVERT(VARCHAR(25),TableDate,120) + '''' + 
            ',' + ISNULL(CAST(Display AS VARCHAR(4)),'NULL') + ')'
        FROM dbo.Table

        EXECUTE xp_cmdshell 'BCP "SELECT * FROM DB.dbo.ScriptTemp" queryout "\\FileSharePath\Script.sql" -T -c -SSERVER'

        DROP TABLE ScriptTemp
END

This creates a script with the table and data (I got the idea from a similar script that Bill Fellows created). Anyway, this solution also works, and is quite handy for people more familiar with TSQL. Thanks again to everyone who answered.

Answer 1

You should check out SQL Server Management Objects (SMO). I have used this to create custom scripting in .NET applications before, but I'm not sure if it supports scripting of data.

From MSDN:

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications.

Answer 2

The wizard is a feature of SQL Management Console and only available from there. It is not part of the SqlConnection class or any part of the .NET Framework itself.

This is kind of like asking where the Fonts dialog is when you instantiate a Word automation object in your code.

Answer 3

The functionality to script out schemas isn't in the sql libraries, its in the visual studio libraries.

E.g. Microsoft.Data.Schema.ScriptDom.Sql, Microsoft.Data.Schema.ScriptDom;

The visual studio team / a guy called Gert Drapers http://blogs.msdn.com/b/gertd/ Worked on something called visual studio team system 2008 database edition - its forgotten about now, but its still there hidden in VS2010 and VS2012 Enterprise edition (or what every its now called). You get the ability to compare schema and data from databases, how did they do this? Microsoft wrote a shed load of classes todo it, which you can use.

There's lots of untapped functionality, of course this doesn't directly answer your question, (SMO) but hopefully it gives you an idea that its possible and someone else has done most of the work.

Have you seen SQLPackage.exe ? That does schema and data all packaged into a zip file.