Extract SQL script code

Public static IList <string> GenerateStoredProcedures ()
{
Int I = 0;
IList <string> list = new List <string> ();
DBUitility db = new DBUitility ();

SqlConnection conn = db. CreateConnection (@ "Data Source =. \ sqlExpress; Initial Catalog = PBCS; Integrated Security = True ");


DataTable dt = db. GetDataAsDataTable (
"Select name, object_id from sys. objects where type = 'p' and charindex ('_', [name]) = 0 and objectproperty (object_id, 'isprocedure ') = 1 ");

Foreach (DataRow dr in dt. Rows)
{
String id = dr ["object_id"]. ToString ();
String name = dr ["name"]. ToString ();

DataTable dtText = db. GetDataAsDataTable (
String. Format ("exec sp_helptext '{0}'", name ));
I ++;

StringBuilder sb = new StringBuilder ();
Sb. appendLine (string. format ("if exists (select * from dbo. sysobjects where id = object_id ('[dbo]. [{0}] ') and OBJECTPROPERTY (id, 'isprocedure') = 1) ", name ));
Sb. AppendLine ("begin ");
Sb. AppendLine (string. Format ("drop procedure [dbo]. [{0}]", name ));
Sb. AppendLine ("end ");
Sb. AppendLine ("go ");
Sb. AppendLine ();
Bool bStart = true;
Foreach (DataRow drText in dtText. Rows)
{
If (bStart & drText [0]. ToString (). Trim ()! = "")
{
BStart = false;
}
If (! BStart)
{
Sb. Append (drText [0]. ToString ());
}
}

Sb. AppendLine ();
Sb. AppendLine ("go ");
Sb. AppendLine ();

Debug. AutoFlush = true;
Debug. WriteLine (sb. ToString ());

List. Add (sb. ToString ());
}
Conn. Dispose ();
Return list;
}

 

 

Public static IList <string> GenerateFunctions ()
{
Int I = 0;
IList <string> list = new List <string> ();
DBUitility db = new DBUitility ();

SqlConnection conn = db. CreateConnection (@ "Data Source =. \ sqlExpress; Initial Catalog = PBCS; Integrated Security = True ");


DataTable dt = db. GetDataAsDataTable (
"Select name, object_id from sys. objects where type = 'tf' and charindex ('_', [name]) = 0 ");

Foreach (DataRow dr in dt. Rows)
{
String id = dr ["object_id"]. ToString ();
String name = dr ["name"]. ToString ();

DataTable dtText = db. GetDataAsDataTable (
String. Format ("exec sp_helptext '{0}'", name ));
I ++;

StringBuilder sb = new StringBuilder ();
Sb. appendLine (string. format ("if exists (select * from dbo. sysobjects where id = object_id ('[dbo]. [{0}] ') ", name ));
Sb. AppendLine ("begin ");
Sb. AppendLine (string. Format ("drop function [dbo]. [{0}]", name ));
Sb. AppendLine ("end ");
Sb. AppendLine ("go ");
Sb. AppendLine ();
Bool bStart = true;
Foreach (DataRow drText in dtText. Rows)
{
If (bStart & drText [0]. ToString (). Trim ()! = "")
{
BStart = false;
}
If (! BStart)
{
Sb. Append (drText [0]. ToString ());
}
}

Sb. AppendLine ();
Sb. AppendLine ("go ");
Sb. AppendLine ();

Debug. AutoFlush = true;
Debug. WriteLine (sb. ToString ());

List. Add (sb. ToString ());
}
Conn. Dispose ();
Return list;
}