Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Parse??


SQL Parse??

Author
Message
Fraggle-805517
Fraggle-805517
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 1509
Hey everyone,

Trying to find a bit of help with a project I am working on.

Basically, we are trying to take a normal ad-hoc query statement:

select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = 'abc'
and t2.colc = 12345

and replace it with something that we could actual review:

select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = @var and t2.colc = @var

So we are pulling a trace an the first statement is what would get shown in the TextData of the trace. We want to be able to store off the trace into a table, but store if off like the 2nd statement so we can group all of them together.

Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.

Thanks,

Fraggle
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
http://www.scalesql.com/cleartrace/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
Actually parsing SQL is quite a simple thing, once you find the right .Net class

Here is one example of what you can do ..

http://tsqlsmells.codeplex.com/

Should be a relatively simple thing to parse the SQL and replace the values with @Var1 @Var2 etc.



Clear Sky SQL
My Blog
Fraggle-805517
Fraggle-805517
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 1509
Yea, I have looked at this. However, it aggregates everything and within the system I am trying to do, add's a lot of unnecessary overhead. I am already putting the data into tables in an aggregation. I just need the parsing part.

Unless you are telling me there is a way to just do the parsing, which I haven't seen.

Fraggle
Fraggle-805517
Fraggle-805517
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 1509
Sadly I am only just now learning OO and C#. And all of my developers are pretty busy for the next few months on a major project. So much so, that I have been told by the development manager that they don't have time for this until March, unless by chance we get the project done early (yeah, when was the last time that happened!).

Any other thoughts?

Thanks,

Fraggle
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38938
i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;

using that, you can tokenize any SQL statement.
i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;
you can do it in a programming language, and there's a brief example on an SSC blog here:
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/


here's the master list of tokens that can appear in a given SQL statement:
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx

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!

Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9502
Fraggle-805517 (10/19/2012)

...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...

SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.
http://msdn.microsoft.com/en-us/library/ms189741.aspx

For example:

SELECT TOP 5 query_stats.query_hash, 
SUM(execution_count) Sum_Execution_Count,
SUM(query_stats.total_worker_time)
/ SUM(query_stats.execution_count) AS Avg_CPU_Time,
max(total_logical_reads) Max_Reads,
MIN(query_stats.statement_text) AS Statement_Text
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY Avg_CPU_Time desc;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Fraggle-805517
Fraggle-805517
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 1509
Eric M Russell (10/23/2012)
Fraggle-805517 (10/19/2012)

...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...

SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.
http://msdn.microsoft.com/en-us/library/ms189741.aspx

For example:

SELECT TOP 5 query_stats.query_hash, 
SUM(execution_count) Sum_Execution_Count,
SUM(query_stats.total_worker_time)
/ SUM(query_stats.execution_count) AS Avg_CPU_Time,
max(total_logical_reads) Max_Reads,
MIN(query_stats.statement_text) AS Statement_Text
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY Avg_CPU_Time desc;




This does get me some stuff, but not everything we will be doing with it. Beside the fact that I can't pull Avg, Stdev, and Median, I am also using this process and an auditing system to see what is being called from specific users when the login to production.

Fraggle
Fraggle-805517
Fraggle-805517
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 1509
Lowell (10/22/2012)
i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;

using that, you can tokenize any SQL statement.
i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;
you can do it in a programming language, and there's a brief example on an SSC blog here:
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/


here's the master list of tokens that can appear in a given SQL statement:
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx


Thanks, I will take a look at this.

Fraggle
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search