Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » SQL Parse??
Add to briefcase

SQL Parse?? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Fraggle-805517
Posted Friday, October 19, 2012 9:25 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:15 AM
Points: 716, Visits: 1,317
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
Post #1374889
opc.three
Posted Sunday, October 21, 2012 8:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 6,730, Visits: 11,783
http://www.scalesql.com/cleartrace/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375241
Dave Ballantyne
Posted Monday, October 22, 2012 12:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
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
Kent user group
Post #1375260
Fraggle-805517
Posted Monday, October 22, 2012 7:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:15 AM
Points: 716, Visits: 1,317
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
Post #1375423
Fraggle-805517
Posted Monday, October 22, 2012 7:38 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:15 AM
Points: 716, Visits: 1,317
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
Post #1375427
Lowell
Posted Monday, October 22, 2012 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 11,647, Visits: 27,753
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1375455
Eric M Russell
Posted Tuesday, October 23, 2012 7:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,164, Visits: 3,340
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;

"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47
Post #1376020
Fraggle-805517
Posted Wednesday, October 24, 2012 7:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:15 AM
Points: 716, Visits: 1,317
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
Post #1376486
Fraggle-805517
Posted Wednesday, October 24, 2012 7:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:15 AM
Points: 716, Visits: 1,317
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
Post #1376487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.