Click here to Skip to main content
15,870,130 members
Articles / Database Development / SQL Server / SQL Server 2008

Generate SQL Script Programmatically

Rate me:
Please Sign up or sign in to vote.
3.76/5 (33 votes)
8 Sep 2009CPOL 267.8K   37   28
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
SQL
exec sp_helptext 'object'

A sample code example is given below.

Sample Code

C#
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

License

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



Comments and Discussions

 
QuestionTitle could be: "Export DDL from a SQL Server Database using C#" Pin
aodennison30-Oct-17 5:25
aodennison30-Oct-17 5:25 
QuestionDoes support tables and Indexes? Pin
SENTHIL MURUGAN D7-Aug-17 21:00
professionalSENTHIL MURUGAN D7-Aug-17 21:00 
QuestionValues to be passed to the function Pin
Sivaprasad SR1-Aug-13 23:59
Sivaprasad SR1-Aug-13 23:59 
GeneralMy vote of 4 Pin
D-Kishore28-Aug-12 22:09
D-Kishore28-Aug-12 22:09 
GeneralMy vote of 2 Pin
Sami Ciit27-Aug-12 1:41
Sami Ciit27-Aug-12 1:41 
QuestionThanks Pin
dharmik.c7-Aug-12 20:50
dharmik.c7-Aug-12 20:50 
AnswerRe: Thanks Pin
Md. Marufuzzaman7-Aug-12 21:04
professionalMd. Marufuzzaman7-Aug-12 21:04 
QuestionGood work Pin
Sunasara Imdadhusen31-Aug-11 22:46
professionalSunasara Imdadhusen31-Aug-11 22:46 
AnswerRe: Good work Pin
Md. Marufuzzaman1-Sep-11 14:08
professionalMd. Marufuzzaman1-Sep-11 14:08 
GeneralKeep going Pin
SiteBuilder11-Apr-11 20:54
professionalSiteBuilder11-Apr-11 20:54 
GeneralRe: Keep going Pin
Md. Marufuzzaman1-Sep-11 14:07
professionalMd. Marufuzzaman1-Sep-11 14:07 
GeneralMy vote of 2 Pin
John Brett24-Sep-09 5:49
John Brett24-Sep-09 5:49 
GeneralRe: My vote of 2 Pin
Md. Marufuzzaman28-Sep-09 7:03
professionalMd. Marufuzzaman28-Sep-09 7:03 
QuestionA bit miffed ??? Pin
Middle Manager15-Sep-09 2:28
Middle Manager15-Sep-09 2:28 
GeneralMy vote of 1 Pin
Andrei Ion Rînea14-Sep-09 22:25
Andrei Ion Rînea14-Sep-09 22:25 
GeneralMy vote of 1 Pin
Paw Jershauge8-Sep-09 8:36
Paw Jershauge8-Sep-09 8:36 
GeneralRe: My vote of 1 Pin
Md. Marufuzzaman28-Sep-09 7:06
professionalMd. Marufuzzaman28-Sep-09 7:06 
GeneralGood. Pin
Abhishek Sur8-Sep-09 8:29
professionalAbhishek Sur8-Sep-09 8:29 
This one is good, but I was expecting more from this. Initially I thought it would generate Insert, update statements automatically.

Another concern, are you sure sp_helptext works with SQL Server CE. I am not sure, but I dont think it does.

Anyways, nice article. Hope to find more from you. 4 from me on this. Big Grin | :-D

Cheers.
Thumbs Up | :thumbsup: Thumbs Up | :thumbsup:

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB

Don't forget to click "Good Answer" if you like to.

GeneralRe: Good. Pin
Md. Marufuzzaman8-Sep-09 8:36
professionalMd. Marufuzzaman8-Sep-09 8:36 
GeneralRe: Good. Pin
Abhishek Sur8-Sep-09 22:14
professionalAbhishek Sur8-Sep-09 22:14 
GeneralMy vote of 1 Pin
voloda28-Sep-09 8:25
voloda28-Sep-09 8:25 
GeneralMy vote of 2 Pin
Fatih Birkan8-Sep-09 8:14
Fatih Birkan8-Sep-09 8:14 
GeneralRe: My vote of 2 Pin
Md. Marufuzzaman8-Sep-09 8:38
professionalMd. Marufuzzaman8-Sep-09 8:38 
GeneralMy vote of 1 Pin
brunomsilva8-Sep-09 7:37
brunomsilva8-Sep-09 7:37 
GeneralRe: My vote of 1 Pin
Md. Marufuzzaman8-Sep-09 8:24
professionalMd. Marufuzzaman8-Sep-09 8:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.