mCompany (ma**@mvestcapital.com) writes:
All we really need to do is split out the various SQL segments (SELECT,
FROM, WHERE, ORDER BY). It's not that difficult to write, but if
there's a quick way to do, we'll use it. Any ideas?
Not that difficult? Boy, you are in for a real treat.
Believe me, I have written Perl code that parses bits of T-SQL, and this
is no simple exercise if you want it to work perfectly. If you can cut
down on the ambitions and miss out on some constructs, the work you need
to put in it, is of moderate size.
The reason why this is difficult is that T-SQL has a wretched ad-hoc
syntax onto which things have been added through the years, and lot of
old syntax retained for compatibility. For instance, do you think these
two statements parse the same?
SELECT 5ee
SELECT 5dd
They don't.
What I needed to was to find all references to table in the code. To this
end I sifted through the code and woke up on keywords that can be followed
by a table name: INSERT, UPDATE, DELETE, FROM and JOIN. FROM is most
tricky, because there may come a list of tables. And in the middle of
there may be a rowset fucntion like OPENQUERY or OPENXML. This is perfectly
legal T-SQL:
SELECT *
FROM a, b, OPENQUERY(REMOTESRV, 'SELECT * FROM a') AS c, d
JOIN e ON a.col = e.col, f
WHERE a.col2 = b.col2
AND a.col3 = c.col3
AND a.col4 = d.col4
AND e.col5 = f.col5
But the code I wrote will not find d and f. It would be crazy to write
code like that.
A tip is that start by doing a pass that eliminates all constants, and
replaces all string literals with simple tokens. String literals here
includes all that is enclosed in '', "" and [].
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
