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