Pulling all object name alone form various Queries

Question

Pulling all object name alone form various Queries

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
April 25, 2016 at 8:53 pm

#310190

Hello DB Experts,
I got a job that we have N number of queries stored in table column name like SQLText that hit by the end user.
Now the real-time requirements is to create the report which Objects are involved in the each Adhoc queries,
Example 1:
Select * from
Tabl1 A join Tabl2e b
on a.id = b.id
Inner Join Table3 c
On c.id = b.id
Where a.id = 1
Expected result is :
Table1,Table2,table3
Example 2:
Select * from Table1,table2 ,table3 where table1.id =table2.id and table3.id = table2.id
Expected result is :
Table1,Table2,table3
Etc....
I tried with Charindex and From till the end of statement but its too complicated and not clearly pull the object name..
Is there any way to pull object name from ADHOC queries in sql??? its bit urgent can anyone have tool or any idea / scripts??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You basically have to rebuild the query parsing engine within SQL Server. I know that there are people who have done this, but most of the time it's being used within a product and isn't available for free. I'm not aware of anyone having the query that you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

What happens when there's the name of an object but it's not referencing the object? Or when it's commented?

Getting object names is easy. Validating that the objects are actually used in the query is different.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 3

from a string representing a query, it's difficult, just as Grant said; you have to tokenize it in a SQL parser. It's quite a bit of work, i did it it in c#.net when i built my own SQL formatter/pretty printer.

it's a bit easier to do it from an execution plan; the xml has all the objects already broekn out, so you can query it with xquery a lot easier, if the plan is an option.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

well i dug a little deeper in my old parser utility.

i expected the tables to be identified as TOKEN_TABLE, but they end up being TOKEN_ID instead; i guess with a connection , TOKEN_ID's eventually map to objectname(object_id), but i'll have to wrestle with this a little more. I really expected objects to get tokenized to TOKEN_TABLE.

string sqltext = @"Select * from

Tabl1 A join Table2 b

on a.id = b.id

Inner Join Table3 c

On c.id = b.id

Where a.id = 1 ";

//this.richTextBox1.Text;

//this.richTextBox2.Clear();

System.Text.StringBuilder stringBuilder = new System.Text.StringBuilder();

//the partser is a nice static method, just call it, no object needed.

Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions myParseOptions = new Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions();

myParseOptions.BatchSeparator = "GO";

myParseOptions.IsQuotedIdentifierSet = true;

Microsoft.SqlServer.Management.SqlParser.Parser.Scanner MyScanner = new Microsoft.SqlServer.Management.SqlParser.Parser.Scanner(myParseOptions);

MyScanner.SetSource(sqltext, 0);

Microsoft.SqlServer.Management.SqlParser.Parser.Tokens token;

int state = 0;

int start;

int end;

bool isPairMatch;

bool isExecAutoParamHelp;

//System.Array Alltokens = Enum.GetValues(typeof(Microsoft.SqlServer.Management.SqlParser.Parser.Tokens));

while ((token = (Microsoft.SqlServer.Management.SqlParser.Parser.Tokens)MyScanner.GetNext(ref state, out start, out end, out isPairMatch, out isExecAutoParamHelp)) != Microsoft.SqlServer.Management.SqlParser.Parser.Tokens.EOF)

{

string str = sqltext.Substring(start, end - start + 1);

Convert.ToInt32(token).ToString();

Console.WriteLine("{0}: {1}: {2}", Convert.ToInt32(token).ToString(), token, str);

//richTextBox2.AppendText(String.Format("{0}: {1}: {2}", Convert.ToInt32(token).ToString(), token, str));

}

here's the representationof the tokens i got:

286: TOKEN_SELECT: Select

42: 42: *

242: TOKEN_FROM: from

171: TOKEN_ID: Tabl1

171: TOKEN_ID: A

157: TOKEN_JOIN: join

171: TOKEN_ID: Table2

171: TOKEN_ID: b

269: TOKEN_ON: on

171: TOKEN_ID: a

46: 46: .

171: TOKEN_ID: id

61: 61: =

171: TOKEN_ID: b

46: 46: .

171: TOKEN_ID: id

152: TOKEN_INNER: Inner

157: TOKEN_JOIN: Join

171: TOKEN_ID: Table3

171: TOKEN_ID: c

269: TOKEN_ON: On

171: TOKEN_ID: c

46: 46: .

171: TOKEN_ID: id

61: 61: =

171: TOKEN_ID: b

46: 46: .

171: TOKEN_ID: id

306: TOKEN_WHERE: Where

171: TOKEN_ID: a

46: 46: .

171: TOKEN_ID: id

61: 61: =

180: TOKEN_INTEGER: 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!