65.9K
CodeProject is changing. Read more.
Home

Generate SQL Script Programmatically

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.80/5 (34 votes)

Sep 8, 2009

CPOL
viewsIcon

270661

How to generate SQL object script using C#.NET

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

  • 8th September, 2009: Initial post