Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

A .NET Windows Application To Generate SQL Server Scripts

2.00/5 (4 votes)
1 Oct 2009CPOL2 min read 52.8K   877  
Generates SQL Server Stored Procedure and Table Defination Scripts

Introduction

If you have to generate create script for bulk number of stored procedures, what will you do?? Use Sp_Helptext sql server inbuilt stored proc. But it consumes time.This Article I how to generate SQL Server Stored Procedure and Create Table defination Script. I have made an Windows Application for Generating SQL Server Stored Procedure and Create Table Create Script with click of single button only.

Image 1

Using the code

Open up New Windows Forms Application Project in Visual Studio 2008. Name it GenerateScripts

On Windows form drag GroupBox Name it DBObjects. Inside Group box drag two radio buttons name them Stored Procedure and Table respectively.

Also drag 2 labels, 3 textboxes and 3 buttons.

Name Labels as Connection String and Path. Buttons as GenerateScripts,Clear and Close

Image 2

On Click of GenerateScript button write the following code

C#
private void btnGenerateScript_Click(object sender, EventArgs e)
{
    if (txtScriptsName.Text.Trim() == "" || txtConnString.Text.Trim() == "") ) //Validating Script and Connection String Textbox
    {
          MessageBox.Show("Enter Script Name");
    }
    else
    {
        string ScriptName = txtScriptsName.Text.Trim();
        char[] a = { '\n' }; 
        string[] Finalstr = ScriptName.Split(a); //Splitting by new line character
        if (rdbStoredProc.Checked) //Code For Stored Proc Script Generation
        {
            string Extn = "";
            foreach (string s in Finalstr)
            {
                string ScriptString = s.TrimEnd('\r');
                Extn = Path.GetExtension(ScriptString);
                if (Extn.ToUpper() == ".SQL") //Checking For Valid Extension
                {
                    GenerateStoredProc(ScriptString.Replace(".sql","")); //Calling Generate Procedure Method
                }
                else
                {
                    MessageBox.Show("Invalid File Extension. Enter Script Name With .sql Extension")
                }
            }
        }
        else // This part will be called when user has checked Table radiobutton
        {
            foreach(string s in Finalstr)
            {
                GenerateTableScripts(s);
            }
        }
    }
    MessageBox.Show(@"Scripts Generated Successfully");
}


//Following Method produces SQL Server Stored Procedure defination scripts.
private void GenerateStoredProc(string ProcName)
{ 
    if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false) //Checking For the Path entered by user exists or not 
    {
        System.IO.Directory.CreateDirectory(txtPath.Text.Trim());  //If Path does not exist then create it
    }
    SqlConnection con = new SqlConnection(txtConnString.Text.Trim());
    SqlCommand cmd = new SqlCommand("SP_HELPTEXT " + ProcName, con);
    cmd.CommandType = CommandType.Text;
    cmd.Connection.Open();

    SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);

    string path = txtPath.Text.Trim() + ProcName + ".sql";       
    FileStream fs = new FileStream(txtPath.Text.Trim() + ProcName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
    StreamWriter sw = new StreamWriter(fs, Encoding.Default);

    while (dr.Read())
    {
        sw.WriteLine(dr[0].ToString());
    }
    sw.Close();
    fs.Close(); 
}

Following Function Method Generates Create Table Script with all the Indexes Script on that table.

C#
private void GenerateTableScripts(string TableName)
{
    if (System.IO.Directory.Exists(txtPath.Text.Trim()) == false)
    {
        System.IO.Directory.CreateDirectory(txtPath.Text.Trim());
    }

    SqlConnection con = new SqlConnection(txtConnString.Text.Trim());
    SqlCommand cmd = new SqlCommand("EXEC sp_ScriptTable " + TableName, con);
    cmd.CommandType = CommandType.Text;
    cmd.Connection.Open();

    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    string path = txtPath.Text.Trim() + TableName + ".sql";

    FileStream fs = new FileStream(txtPath.Text.Trim() + TableName + ".sql", FileMode.OpenOrCreate, FileAccess.ReadWrite);
    StreamWriter sw = new StreamWriter(fs, Encoding.Default);

    while (dr.Read())
    {
        sw.WriteLine(dr[0].ToString());
    }
    sw.Close();
    fs.Close();
}    

The above method uses SP_ScriptTable for generating Create Table Defination script. Run the following Sql Server Script in your SQL Server database. This Script will run only in SQL Server 2005 and above.

SQL
CREATE PROCEDURE sp_ScriptTable  
(  
    @TableName SYSNAME,  
    @IncludeConstraints BIT = 1,  
    @IncludeIndexes BIT = 1,  
    @NewTableName SYSNAME = NULL,  
    @UseSystemDataTypes BIT = 0  
)  
AS  
BEGIN   
    DECLARE @MainDefinition TABLE  
    (  
        FieldValue VARCHAR(200)  
    )  
    DECLARE @DBName SYSNAME  
    DECLARE @ClusteredPK BIT  
    DECLARE @TableSchema NVARCHAR(255)  
    SET @DBName = DB_NAME(DB_ID())  
    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)  
    DECLARE @ShowFields TABLE  
    (  
        FieldID INT IDENTITY(1,1),  
        DatabaseName VARCHAR(100),  
        TableOwner VARCHAR(100),  
        TableName VARCHAR(100),  
        FieldName VARCHAR(100),  
        ColumnPosition INT,  
        ColumnDefaultValue VARCHAR(100),  
        ColumnDefaultName VARCHAR(100),  
        IsNullable BIT,  
        DataType VARCHAR(100),  
        MaxLength INT,  
        NumericPrecision INT,  
        NumericScale INT,  
        DomainName VARCHAR(100),  
        FieldListingName VARCHAR(110),  
        FieldDefinition CHAR(1),  
        IdentityColumn BIT,  
        IdentitySeed INT,  
        IdentityIncrement INT,  
        IsCharColumn BIT  
    )  
    DECLARE @HoldingArea TABLE  
    (  
        FldID SMALLINT IDENTITY(1,1),  
        Flds VARCHAR(4000),  
        FldValue CHAR(1) DEFAULT(0)  
    )  
    DECLARE @PKObjectID TABLE  
    (  
        ObjectID INT  
    )  
    DECLARE @Uniques TABLE  
    (  
        ObjectID INT  
    )  
    DECLARE @HoldingAreaValues TABLE  
    (  
        FldID SMALLINT IDENTITY(1,1),  
        Flds VARCHAR(4000),  
        FldValue CHAR(1) DEFAULT(0)  
    )  
    DECLARE @Definition TABLE  
    (  
        DefinitionID SMALLINT IDENTITY(1,1),  
        FieldValue VARCHAR(200)  
    )  
    INSERT INTO @ShowFields  
    (         
        DatabaseName,  
        TableOwner,  
        TableName,  
        FieldName,  
        ColumnPosition,  
        ColumnDefaultValue,  
        ColumnDefaultName,  
        IsNullable,  
        DataType,  
        MaxLength,  
        NumericPrecision,  
        NumericScale,  
        DomainName,  
        FieldListingName,  
        FieldDefinition,  
        IdentityColumn,  
        IdentitySeed,  
        IdentityIncrement,  
        IsCharColumn  
    )  
    SELECT  
        DB_NAME(),  
        TABLE_SCHEMA,  
        TABLE_NAME,  
        COLUMN_NAME,  
        CAST(ORDINAL_POSITION AS INT),  
        COLUMN_DEFAULT,  
        dobj.name AS ColumnDefaultName,  
        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,  
        DATA_TYPE,  
        CAST(CHARACTER_MAXIMUM_LENGTH AS INT),  
        CAST(NUMERIC_PRECISION AS INT),  
        CAST(NUMERIC_SCALE AS INT),  
        DOMAIN_NAME,  
        COLUMN_NAME + ',','' AS FieldDefinition,  
        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,  
        CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,  
        CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,  
        CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn  
    FROM  
        INFORMATION_SCHEMA.COLUMNS c  
        JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name  
        LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name  
        JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name  
        LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'  
    WHERE c.TABLE_NAME = @TableName  
    ORDER BY  
        c.TABLE_NAME, c.ORDINAL_POSITION  
    SELECT TOP 1 @TableSchema = TableOwner  
    FROM @ShowFields  
    INSERT INTO @HoldingArea (Flds) VALUES('(')  
    INSERT INTO @Definition(FieldValue)  
    VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)  
    INSERT INTO @Definition(FieldValue)  
    VALUES('(')  
    INSERT INTO @Definition(FieldValue)  
        SELECT  
           CHAR(10) + FieldName + ' ' +  
               CASE  
                   WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN 
                        DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END  
                   ELSE UPPER(DataType) +  
                      CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +  
                      CASE WHEN IdentityColumn = 1 THEN 
                                ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +  
                      CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +  
                      CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
                                'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END  
                   END +   
               CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END  
        FROM    @ShowFields   
        IF @IncludeConstraints = 1  
        BEGIN  
            INSERT INTO @Definition(FieldValue)  
            SELECT  
            ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'  
            FROM  
            (  
                SELECT  
                ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,  
                REVERSE(SUBSTRING(REVERSE((  
                SELECT cp.name + ','  
                FROM  
                sys.foreign_key_columns fkc  
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id  
                WHERE fkc.constraint_object_id = fk.object_id  
                FOR XML PATH('')  
                )), 2, 8000)) ParentColumns,  
                REVERSE(SUBSTRING(REVERSE((  
                SELECT cr.name + ','  
                FROM  
                sys.foreign_key_columns fkc  
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id  
                WHERE fkc.constraint_object_id = fk.object_id  
                FOR XML PATH('')  
                )), 2, 8000)) ReferencedColumns  
                FROM sys.foreign_keys fk  
            ) a  
            WHERE ParentObject = @TableName  
            INSERT INTO @Definition(FieldValue)  
            SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints  
            WHERE OBJECT_NAME(parent_object_id) = @TableName  
        INSERT INTO @PKObjectID(ObjectID)  
        SELECT DISTINCT  
            PKObject = cco.object_id  
        FROM  
            sys.key_constraints cco  
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
        WHERE  
            OBJECT_NAME(parent_object_id) = @TableName    AND     
            i.type = 1 AND  
            is_primary_key = 1  
        INSERT INTO @Uniques(ObjectID)  
        SELECT DISTINCT  
            PKObject = cco.object_id  
        FROM  
            sys.key_constraints cco  
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
        WHERE  
            OBJECT_NAME(parent_object_id) = @TableName AND         
            i.type = 2 AND  
            is_primary_key = 0 AND  
            is_unique_constraint = 1   
        SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END  
        INSERT INTO @Definition(FieldValue)  
        SELECT    ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN 
                  ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END  
                            WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +  
        REVERSE(SUBSTRING(REVERSE((  
            SELECT  
                c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','  
            FROM  
                sys.key_constraints ccok  
                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id  
                LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id  
                LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id  
            WHERE  
                i.object_id = ccok.parent_object_id AND  
                ccok.object_id = cco.object_id  
            FOR XML PATH('')  
        )), 2, 8000)) + ')'  
        FROM  
            sys.key_constraints cco  
            LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID  
            LEFT JOIN @Uniques u ON cco.object_id = u.objectID  
        WHERE  
            OBJECT_NAME(cco.parent_object_id) = @TableName  
        END  
        INSERT INTO @Definition(FieldValue)  
        VALUES(')')  
        IF @IncludeIndexes = 1  
        BEGIN  
            INSERT INTO @Definition(FieldValue)  
            SELECT  
                'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +  
                REVERSE(SUBSTRING(REVERSE((  
                    SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','  
                    FROM  
                        sys.index_columns sc  
                        JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
                    WHERE  
                        OBJECT_NAME(sc.object_id) = @TableName AND  
                        sc.object_id = i.object_id AND  
                        sc.index_id = i.index_id  
                    ORDER BY index_column_id ASC  
                    FOR XML PATH('')  
            )), 2, 8000)) + ')'  
            FROM sys.indexes i  
            WHERE  
                OBJECT_NAME(object_id) = @TableName  
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1  
                AND is_unique_constraint = 0  
                AND is_primary_key = 0  
        END  
            INSERT INTO @MainDefinition(FieldValue)  
            SELECT FieldValue FROM @Definition  
            ORDER BY DefinitionID ASC  
        SELECT * FROM @MainDefinition  
END                                                   

Explaination


I have used two classes FileStream class and Stream Class.The FileStream class is derived from Stream class. This class is used for reading from and writing to files such as bytes, characters, strings, and other data-type values to a file. Other class I have used is StreamWriter. The StreamWriter class's Write and WriteLine members write to a file.
In this above code, I have used FileStream class to create a sql file and StreamWrite to write text to the sql file.

In the above Code, you can see that two methods are defined named GenerateStoredProc and GenerateTableScript.In the First Method i have used SP_HELPTEXT Command that generates the stored Procedure scripts and in the second method i have called an stored Procedure that generates create table script and saves into the path which is given by the user.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)