I want to build a sql parser - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I want to build a sql parser

Author

Topic

fafastrungen
Starting Member

5 Posts

Posted - 2007-07-20 : 06:45:54

Hi, I'd like to build a sql parse to understand stored procedures.
I have too many stored procedures to check one by one, so I need a tool that makes this but all tools I've found can not achieve these tasks in a safe way, so I' thought to build my own sql parser to understand the sp's bodies.
What I need is to know where are stored all the SQL instructions, starting with the basic instructions like SELECT, INSERT, and so on and finishing with the Sql Server own instructions like GETDATE, SET NOCOUNT and so on.
I need the complete list of the sql instructions to understand a full sql.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-20 : 07:06:00

You should read Books On Line, from cover to cover. Then to see the text in a stored procedure use sys.sql_modules

Jim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-20 : 07:25:52

build your own sql parser??? good luck.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mattyblah
Starting Member

49 Posts

Posted - 2007-07-20 : 11:28:25

The ability to write a sql parser would far surpass the ability to understand stored procedures. but, if you want, take a look at some compiler design books. it should help you understand how the parsing process works with programming languages.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-20 : 13:43:13

building a parser for any language is non-trivial. have you ever done it before?

elsasoft.org

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-07-20 : 17:04:28

quote:
Originally posted by fafastrungen

Hi, I'd like to build a sql parse to understand stored procedures.
I have too many stored procedures to check one by one, so I need a tool that makes this but all tools I've found can not achieve these tasks in a safe way, so I' thought to build my own sql parser to understand the sp's bodies.
What I need is to know where are stored all the SQL instructions, starting with the basic instructions like SELECT, INSERT, and so on and finishing with the Sql Server own instructions like GETDATE, SET NOCOUNT and so on.
I need the complete list of the sql instructions to understand a full sql.

Are you insane?

Haywood
Posting Yak Master

221 Posts

Posted - 2007-07-20 : 17:07:05

quote:
Originally posted by rudesyle

Are you insane?

Only ask, if you really want to know...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-20 : 17:44:25

Good luck with your little sql parser project.

Be sure to post the code when you're done.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 17:54:50

And you want this to be done in SQL Server?

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2007-07-20 : 18:42:14

Lol (to comments and question).

The best SQL Parser is likely to be you. As mentioned, read Books Online. Reading cover to cover is likely to be excessive for your needs. Just run through some SQL tutorials (such as http://www.w3schools.com/sql/default.asp) if you don't know the basics and then work your way through the procedures, looking up each function & command that is alien to you in Books Online as you come across them. There is no short cut to knowledge - this is a good thing otherwise the job market would be pretty lousy.

fafastrungen
Starting Member

5 Posts

Posted - 2007-07-23 : 03:27:59

In fact I need to check the stored procedures and how to optimize them, that's why I need to understand the SQL code becouse SP's doesn't respect a fixed way in their bodies.
This could be a basic SP:

Alter Procedure SP1 As
Set NoCount ON
Select * from Sys.Tables
Go

but in the text's body this is the result:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[SP1] As
Set NoCount ON
Select * from Sys.Tables

so if I need to optimize it, I need to understand the code inside it.

And yes, I'm insane, that's why I've choosen a job with computers.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 06:23:31

i don't get it... it's the same body.
what's your problem?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-23 : 07:29:06

If you want to optimize queries, put the body of a sproc in the query analyzer and hit ctrl + L, This will give you SQL's execution plan for the query. Look up all the different seeks, scans, etc., and this will give you a starting point to see what SQL is doing so that you can then optimize.

Jim

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-07-23 : 07:31:40

I'd agree with everyone here that writing a parser isn't the way to accomplish your goal. If you really do want to write a parser though I'd start here: http://www.devincook.com/goldparser/

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-07-23 : 10:33:55

i'd start with building a new OS first...

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-23 : 15:35:22

Sounds like you just want the ability to interrogate all of the sprocs based on a key word

http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

fafastrungen
Starting Member

5 Posts

Posted - 2007-07-24 : 04:02:50

I have many SPs on a server so I can't go one by one manually, I need something that do the hard work.
These SPs are created with CodeSmith, but CodeSmith doesn't generate optimized SPs, thats why I need to check the SPs.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-24 : 04:31:18

oh so you want to optimize your sprocs?

you'll have to go through one by one
no parser will help you there.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pootle_flump

1064 Posts

Posted - 2007-07-24 : 06:44:53

quote:
Originally posted by spirit1

you'll have to go through one by one
no parser will help you there.

Sounds a bit like hard work that spirit. You would have thought SQL Server could optimise them itself if it was any good. Use Oracle!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-24 : 07:32:51

silly me... i don't know what i was thinking

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-24 : 10:39:45

procs are easy to optimize. just comment out the body. then they will run really fast.

elsasoft.org

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources