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;
}
{
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;
}
{
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;
}