CODEWITHSUNDEEP
c#sql-serverstring
Hossein Mahmoodi
Hossein Mahmoodi

Reputation: 117

How to get table name from string that contain SELECT statement

i have a string that contain a sql command,

something like this:

strCommand = "Select [Feild1], [Feild2] From TableName Order By [Feild1] desc" ;

How can find table name in this string?

Upvotes: 5

Views: 13539

Answers (7)

ntmb23
ntmb23

Reputation: 51

ISun's answer met my needs but one change is required to get the table name:

string table = strCommand.ToLower().Substring(strCommand.IndexOf("FROM".ToLower())).Split(' ')[1];

not 

string table = strCommand.ToLower().Substring(strCommand.IndexOf("FROM".ToLower())).Split(' ')[0];

Upvotes: 1

Richard
Richard

Reputation: 30618

The solutions so far have all gone with the searching within strings approach. You've not mentioned if your SQL queries will always look similar, but there are many variants of a query to include which these solutions will break on. Consider...

  • SELECT Field1, Field2 FROM TableName
  • SELECT Field1, Field2 FROM [TableName]
  • SELECT Field1, Field2 FROM dbo.TableName
  • SELECT Field1, Field2 FROM Table1Name, Table2Name

If the query you're trying to parse is one you have the database for, you can get SQL server to do the hard work of parsing the query for you, instead of trying to account for all the cases in SQL. You can execute a query using SET SHOWPLAN_ALL ON, which will produce a table of the query plan. You can then analyse the Arguments column, which contains all of the fields the query will involve in a standard format. An example program is below:

SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SET SHOWPLAN_ALL ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT [Field1], [Field2] FROM [TableName]";

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

Regex objectRegex = new Regex(@"^OBJECT:\(\[(?<database>[^\]]+)\]\.\[(?<schema>[^\]]+)\]\.\[(?<table>[^\]]+)\]\.\[(?<field>[^\]]+)\]\)$", RegexOptions.ExplicitCapture);

List<string> lstTables = new List<string>();
foreach (DataRow row in dt.Rows)
{
    string argument = row["Argument"].ToString();
    if (!String.IsNullOrEmpty(argument))
    {
        Match m = objectRegex.Match(argument);
        if (m.Success)
        {
            string table = m.Groups["schema"] + "." + m.Groups["table"];
            if (!lstTables.Contains(table))
            {
                lstTables.Add(table);
            }
        }
    }
}

Console.WriteLine("Query uses the following tables: " + String.Join(", ", lstTables));

This will deal with all forms of query name and return all tables which are involved in the query, no matter how they are included.

Upvotes: 6

yogi
yogi

Reputation: 19591

If you want a solution in SQL, try this

declare @q varchar(1000) = 'Select [Feild1], [Feild2] From TableName Order By [Feild1] desc',
        @tableName varchar(100) = '',
        @temp varchar(1000),
        @temp2 char(1)

declare @frmIndex int = CHARINDEX('From', @q, 0);
declare @flag int = 0, @counter int = 1;
select @temp = SUBSTRING(@q, @frmIndex, len(@q))

set @temp = LTRIM(REPLACE(@temp,'From',''))

while(@flag <> 1)
begin
    set @temp2 = SUBSTRING(@temp, @counter, 1)
    if(@temp2 = ' ')
        set @flag = 1
    select @tableName = @tableName + @temp2
    set @counter = @counter + 1
end

select @tableName as TableName

Upvotes: 0

Matan Shahar
Matan Shahar

Reputation: 3240

You can use the substring (This way it does not matter how many column you have to select)

string table = strCommand.ToLower().Substring(strCommand.IndexOf("FROM".ToLower())).Split(' ')[0];

Upvotes: 1

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48568

This is the Method which gives us tablename just change the SQL query string, connection String

Works with simple query, joins too

public static List<string> getTablenames(string connString, string QueryString)
{
    SqlConnection con = new SqlConnection(connString);
    con.Open();
    DataTable dt = con.GetSchema("Tables");

    List<string> getTableName = new List<string>();
    List<string> tablenames = new List<string>();

    foreach (DataRow dr in dt.Rows)
       tablenames.Add(dr[2].ToString());

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        string myTable = tablenames[i];
        Boolean checkMyTable = QueryString.Contains(myTable);
        if (checkMyTable == true)
            getTableName.Add(myTable);
    }
    con.Close();
    return getTableName;
}

Upvotes: 1

Anna Billstrom
Anna Billstrom

Reputation: 2492

I would say- what is after "From" as a more reliable way of getting the table name. Loop through the array created, when you reach "From", the next one is the table.

Upvotes: 1

eyossi
eyossi

Reputation: 4340

If this is the same pattern all of the time then:

 string tableName = strCommand.Split(' ', strCommand)[4];

but if you can add / remove fields just iterate through the splitted string and search for "From", and the next string will be your table name

Upvotes: 2

Related Questions