I am trying to parse through hundreds of stored procedures to specifically grab their output variables "@FirstName", which tables they use, and which fields they pull from "MyTbl.FirstName". I am able to collect the variables pretty easily but I'm having trouble collecting the table names. Could anyone help?
So far I've been able to pull most of these fields by parsing through the SQL files using the StreamReader and collecting information line by line, for example if a line contains output, then I know the first text in the line is most likely the @Variable.
@Address1 varchar(45) output,
@Address2 varchar(45) output,
@City varchar(35) output,
@State varchar(2) output,
@Zip varchar(10) output
From there I can store the @Variable into a dictionary and if any line contains the @Variable and also contains a '=' then I know we have a match as to which field it corresponds to.
@Address1 = c.Address,
@Address2 = c.AddressSecondLine,
@City = c.City,
@State = c.State,
@Zip = c.ZipOrPostalCode
Now I'm just having issues gathering the table name. I can easily parse the table alias off the field name but I'm having issues matching the alias with a table name. Does anyone know of a good way to do this? Here's what I've been trying so far:
FROM Table.dbo.SalesStuff ss
LEFT OUTER JOIN Table.dbo.Customer c ON ss.CustNo = c.CustNo
Left JOIN Table.dbo.Vending v on @tmpVin = v.vin
Code:
keyColl = tables.Keys;
foreach (string var in keyColl)
{
if (line.Contains(" " + var + '\r') || line.Contains(" " + var + " ") || line.Contains(" " + var + ((char)13)) || line.Contains(" " + var + Environment.NewLine))
{
tables[var] = line.ToString();
break;
}
}
I thought that this would match the table alias since most aliases are a letter, followed by a line break, but so far I haven't been able to get any of the table names... Does anyone have an idea?