Generate SQL Script Programmatically

Introduction

This article will give you an idea of how easily you can generate the SQL server database object script using C#. NET.

Background

This article will be helpful for developers who want to generate the SQL Server database objects script like stored procedure, views, etc.

Using the Code

It’s a very easy way... I hope that you will agree with me that the flexibility of Microsoft products to integrate with your custom application is outstanding. Microsoft SQL Server provides a special stored procedure "sp_helptext", This stored procedure allows you to get the description of a given object.

Here I write a function to get the SQL script from a given object. You just need to provide three parameters:

• Connection string
• Object name
• Object type (I used this to determine whether it's a Table or not)

Syntax

exec sp_helptext 'object'

A sample code example is given below.

Sample Code

public string GetScript(string strConnectionString
                      , string strObject
                      , int ObjType)
{
    string strScript = null;
    int intCounter = 0;
    if (ObjType != 0)
    {
        ObjSqlConnection = new SqlConnection(strConnectionString.Trim());

        try
        {
            ObjDataSet = new DataSet();
            ObjSqlCommand = new SqlCommand("exec sp_helptext 
				[" + strObject + "]", ObjSqlConnection);
            ObjSqlDataAdapter = new SqlDataAdapter();
            ObjSqlDataAdapter.SelectCommand = ObjSqlCommand;
            ObjSqlDataAdapter.Fill(ObjDataSet);

            foreach (DataRow ObjDataRow in ObjDataSet.Tables[0].Rows)
            {
                strScript += Convert.ToString(ObjDataSet.Tables[0].Rows[intCounter][0]);
                intCounter++;
            }
        }
        catch (Exception ex)
        {
           strScript = ex.Message.ToString();
        }
        finally
        {
            ObjSqlDataAdapter = null;
            ObjSqlCommand = null;
            ObjSqlConnection = null;
        }
    }

    return strScript;
}

Points of Interest

The stored procedure sp_helptext will not allow to give you any table description.

Conclusion

I hope that this article will be helpful to you. Enjoy!

History

• 8^th September, 2009: Initial post