private static string GetSQLDropScript(string sql)
{
try
{
//Strip out comments
var regexObj = new System.Text.RegularExpressions.Regex(@"/\*(?>(?:(?!\*/|/\*).)*)(?>(?:/\*(?>(?:(?!\*/|/\*).)*)\*/(?>(?:(?!\*/|/\*).)*))*).*?\*/|--.*?\r?[\n]", System.Text.RegularExpressions.RegexOptions.Singleline);
var matchResult = regexObj.Match(sql);
while (matchResult.Success)
{
sql = sql.Remove(matchResult.Index, matchResult.Length).Insert(matchResult.Index, new string(' ', matchResult.Length));
matchResult = matchResult.NextMatch();
}
var dropObjectName = SQLStripObjectName(sql, "CREATE PROCEDURE");
if (!string.IsNullOrEmpty(dropObjectName))
return "if exists (select * from dbo.sysobjects where id = object_id(N'" + dropObjectName + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" + Environment.NewLine + "drop procedure " + dropObjectName;
dropObjectName = SQLStripObjectName(sql, "CREATE PROC");
if (!string.IsNullOrEmpty(dropObjectName))
return "if exists (select * from dbo.sysobjects where id = object_id(N'" + dropObjectName + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" + Environment.NewLine + "drop procedure " + dropObjectName;
dropObjectName = SQLStripObjectName(sql, "CREATE FUNCTION");
if (!string.IsNullOrEmpty(dropObjectName))
return "if exists (select * from dbo.sysobjects where id = object_id(N'" + dropObjectName + "') and [xtype] IN ('FN', 'IF', 'TF'))" + Environment.NewLine + "drop function " + dropObjectName;
dropObjectName = SQLStripObjectName(sql, "CREATE VIEW");
if (!string.IsNullOrEmpty(dropObjectName))
return "if exists (select * from dbo.sysobjects where id = object_id(N'" + dropObjectName + "') and [xtype] = 'V')" + Environment.NewLine + "drop view " + dropObjectName;
return null;
}
catch (Exception ex)
{
return null;
}
}
