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