static List <KeyValuePair <string, object> > GetSPData(string spName)
{
var colTypes = new List <KeyValuePair <string, object> >();
var existing = new HashSet <string>();
SqlCommand sqlcom = new SqlCommand("sp_helptext");
sqlcom.CommandType = CommandType.StoredProcedure;
//var query = @"SELECT definition FROM sys.sql_modules WHERE object_id = (OBJECT_ID(N'" + spName + "'))";
var query = @"SELECT definition FROM sys.sql_modules WHERE object_id = (OBJECT_ID(@0))";
List <SqlParameter> sp = new List <SqlParameter>();
sp.Add(new SqlParameter("@0", spName));
var data = SQLQueryFunction.GetData(query, "", sp);
var str = data.AsString().ToLower();
int start = str.IndexOf('@');
while (start > 0)
{
int end1 = str.IndexOf(' ', start + 1);
int end2 = str.IndexOfAny(" \n\t".ToCharArray(), end1 + 1);
if (end1 < 0 || end2 < 0)
{
break;
}
var paramName = str.Substring(start + 1, end1 - start).Trim();
if (existing.Contains(paramName))
{
break;
}
existing.Add(paramName);
var paramType = str.Substring(end1 + 1, end2 - end1).Trim();
try
{
var sqlType = SQLQueryFunction.StringToSqlDbType(paramType);
colTypes.Add(new KeyValuePair <string, object>(paramName, sqlType));
}
catch (Exception)
{
var colData = SQLQueryFunction.GetColumnUserData(paramType);
colTypes.Add(new KeyValuePair <string, object>(paramName, colData));
}
start = str.IndexOf('@', end2 + 1);
}
return(colTypes);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
CSCS_SQL.CheckConnectionString(script, m_name);
switch (m_mode)
{
case Mode.SP_DESC:
Utils.CheckArgs(args.Count, 1, m_name);
var spName = "sp_helptext";
var argName = Utils.GetSafeString(args, 0);
List <KeyValuePair <string, object> > spParams = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@objname", argName)
};
var results = SQLSPFunction.ExecuteSP(spName, spParams);
if (results.Type == Variable.VarType.ARRAY && results.Tuple.Count >= 1 &&
results.Tuple[0].Type == Variable.VarType.ARRAY && results.Tuple[0].Count >= 1)
{
var r = results.Tuple[0].Tuple[0].AsString();
var res = System.Text.RegularExpressions.Regex.Replace(r, @"\s{2,}", " ");
return(new Variable(res));
}
return(results);
case Mode.TABLES:
return(RemoveListEntries(SQLQueryFunction.GetData("SELECT name FROM sysobjects WHERE xtype = 'U'",
null, null, false)));
case Mode.PROCEDURES:
return(RemoveListEntries(SQLQueryFunction.GetData("SELECT NAME from SYS.PROCEDURES",
null, null, false)));
}
return(Variable.EmptyInstance);
}
