TSQL parser

Question

Post reply

TSQL parser

kyounger

SSC Rookie

Points: 34
More actions
February 26, 2010 at 10:24 am

#217332

I am wanting to know if anyone has seen a script/program that pulls column names from a query.
Basically, I could put any INSERT, UPDATE, SELECT, etc. query, and it would spit out a list of [tablename].[columname]'s, including those used in WHERE, GROUP BY, ORDER BY, etc.
Otherwise, having to do this by hand on some 500 sprocs, each containing a dozen query batches is going to be a real pain.
Thanks in advance for any advice you have!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

What's the ultimate goal of this task?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

LutzM SSC Guru Points: 107049 More actions · Answer 2

You could use SET STATISTICS XML ON before your query.

This will result in an XML execution plan.

Then query that xml file, e.g. for OutputList/@Column, Identifier a.s.o.

I'm not sure if there's an easier solution...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

You can get the SQL statements defining the object from the OBJECT_DEFINITION function. I'm not sure I'd like to write a parser for it though.

An easier way might be to use the new dependency information in 2008. See Understanding SQL Dependencies.

That page of Books Online includes links to:

sys.sql_expression_dependencies

sys.dm_sql_referencing entities

sys.dm_sql_referenced_entities

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

kyounger (2/26/2010)
I am wanting to know if anyone has seen a script/program that pulls column names from a query.
Basically, I could put any INSERT, UPDATE, SELECT, etc. query, and it would spit out a list of [tablename].[columname]'s, including those used in WHERE, GROUP BY, ORDER BY, etc.
Otherwise, having to do this by hand on some 500 sprocs, each containing a dozen query batches is going to be a real pain.
Thanks in advance for any advice you have!

Hmmmm.... not sure IIRC but I believe you can get most of what you need by looking up the ID for the procedure from sys.columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jeff Moden (2/26/2010)
Hmmmm.... not sure IIRC but I believe you can get most of what you need by looking up the ID for the procedure from sys.columns.

Not as far as I know 🙁

SELECT *

FROM AdventureWorks.sys.procedures P

JOIN AdventureWorks.sys.columns C

ON C.object_id = P.object_id;

...returns no rows 🙁 🙁

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kyounger SSC Rookie Points: 34 More actions · Answer 6

The SHOWPLAN_XML solution is exactly what I needed. I also found this: http://www.sqlservercentral.com/articles/Administration/3214/

For those of you that were curious, I am working on a method to parse the execution tree of a nightly process and document the column dependencies.

I did run into an interesting issue. The namespace on the xml seems to cause issues when trying to use it with various xquery methods. It's almost as if the db engine is trying to execute the plan...

If I remove the namespace attribute, things work as expected.

Thanks again for the help.