How to extract word from text file

Question

I have one store procedure text file. I have many table and column name.

How to get table name and column name from text file.

For example

select t1.name, t2.roleid, t3.department, t4.country from dbo.user t1 inner join
dbo.role t2 on t1.roleid = t2.roleid inner join dbo.tbldepartment t3 on t1.departmentid= t3.departmentid inner join dbo.country t4 on t1.countryid = t4.countryid

Result

Table name => dbo.user
Coulmn name => name

table name => dbo.role
column name =>roleid

Table name => dbo.tbldepartment
column name => departmentid

Table name =>dbo.country
column name => countryid

[so] is *not* a free code writing service. You are expected to try to **write the code yourself**. After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a [**Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve). I suggest reading [ask] a good question and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). Also, be sure to take the [tour]. — Igor, Mar 08 '17 at 11:08
tables would be dependencies. can you connect to a sql server instance and actually create those stored procedures, so you could eventually visit their AST somehow? if you tell us what you tried and which tools/libraries you have at your disposal to solve this, it can still become a very good question. — Cee McSharpface, Mar 08 '17 at 11:09
and elaborate on the requirements: are you limiting to single-statement procedures, or can they be anything complex? i did this once for tables and views, and would second this if there was a way to do it on the columns level — Cee McSharpface, Mar 08 '17 at 11:14
I have complex stored procedure. so only i need to get the exact table and column name via .net tool. — balaji sachin, Mar 08 '17 at 11:16
I have tried read the text file find the dbo. related words. But i cant able to move this — balaji sachin, Mar 08 '17 at 11:17
do you have just the text, or do you have an open `SqlDbConnection` to a database where the procedures live? — Cee McSharpface, Mar 08 '17 at 11:19
you need a parser for the tsql language to do this properly. simple indexof/substring mangling will never cover all cases necessary for a complete solution. — Cee McSharpface, Mar 08 '17 at 11:20
@balajisachin I'm interested in your question will try this if i found some solution to do so — J.SMTBCJ15, Mar 08 '17 at 11:25

J.SMTBCJ15 · Accepted Answer · 2017-03-08T13:10:56.180

Some code ...

string file = File.ReadAllText(@"E:\Projects\testApp\testApp\bin\Debug\file.txt");
string textAfterFrom = file.After(" from ");
string textBeforeFrom = file.Before(" from ");
string[] textArrayAfterSelect = textBeforeFrom.After("select").Split(',');

List<ColumnTableRelation> ListofTable = new List<ColumnTableRelation>();
ColumnTableRelation objRel = new ColumnTableRelation();

string[] splitTables = textAfterFrom.Split('\n');

foreach (string tableNames in splitTables)
{
   objRel = new ColumnTableRelation();
   if (!string.IsNullOrEmpty(tableNames) & !tableNames.Equals("\r"))
   {
       if (tableNames.Contains("dbo."))
       {
          if (tableNames.Contains("inner join"))
          {
            objRel.Tablename = tableNames.Between("inner join", " on ").Trim();
          }
          else
          {
            objRel.Tablename = tableNames.Before("\r").Trim();
          }
       }
       ListofTable.Add(objRel);
   }
}

foreach (var item in ListofTable)
{
   string[] stringArray = textArrayAfterSelect;
   string value = item.Tablename.After(" ");
   var matchingvalues = stringArray.Where(stringToCheck => stringToCheck.Contains(value));
   List<string> listString = new List<string>();
   foreach (var match in matchingvalues)
   {
      listString.Add(match);
   }
   item.ColumnNames = listString;
}
StringBuilder builder = new StringBuilder();
foreach (var item in ListofTable)
{
   builder.Append(Environment.NewLine);
   builder.Append("Table name => " + item.Tablename + Environment.NewLine);
   foreach (var columns in item.ColumnNames)
   {
      builder.Append("Column name => " + columns + Environment.NewLine);
   }
}
string yourResult = builder.ToString();
MessageBox.Show(yourResult);

You will get your output like this

You can have your Extension Class SubstringExtensions from this link https://www.dotnetperls.com/between-before-after

Also add this class to you project

public class ColumnTableRelation
{
    public string Tablename { get; set; }
    public List<string> ColumnNames { get; set; }
}

Any Queries will be appreciated

I have tried this with

select t1.name, t2.roleid, t3.department, t4.country from 
dbo.user t1 
inner join dbo.role t2 on t1.roleid = t2.roleid 
inner join dbo.tbldepartment t3 on t1.departmentid= t3.departmentid 
inner join dbo.country t4 on t1.countryid = t4.countryid

Thank you. That code working is fine. But is works small query. If i have sub query or insert statement. i cant get value. Please give a suggestion on this — balaji sachin, Mar 08 '17 at 13:19
I have sub queries, update statements, insert statements and delete queries in my stored procedure — balaji sachin, Mar 09 '17 at 06:46
@balajisachin Above code will work with simplest case which you mentioned earlier yesterday. You can modify this code according to your requirement. PLUS if I would have to create a output like you want i would definitely do this with SQL , but you mentioned that you want it in c# — J.SMTBCJ15, Mar 09 '17 at 06:51
@SMTBCJ15 Now i am working on getting table name and column name from old stored procedure. I have 100+ stored procedure. So i am prepared tool for getting table and matched column name from stored procedure — balaji sachin, Mar 09 '17 at 07:28
why you cannot use sql for this i.e make some function and pass procedure name to it and it will give you result and in your function convert your rows into columns (search for **PIVOT**) and after this you'll not have to do much at code end — J.SMTBCJ15, Mar 09 '17 at 07:46
Sorry i dont want pivot table. I need what are the column and table used our stored procedure for the documentation purpose and data migration — balaji sachin, Mar 09 '17 at 09:49

How to extract word from text file

1 Answers1