question

nosinet avatar image
nosinet asked

Fully qualify SQL statement using SQL server

Dear gods of SQL and masters of the Database please help or give me pointers so I may help myself. What I want, what I want, what I really really want is to be able is take a SQL statement and fully qualify it without the use of 3rd party software (which I know exists). I am a good way there using sys tables, char/patindex’s, etc but ultimately fall over when it comes to comes parsing the potential funkiness of the SQL strings where it comes to completely separating/isolating literal strings from block comments from line comments ifyouknowhatImean. I’ve built solutions that work but not one yet that works 100% 100% of the time and thats what I need. It feels like I need some sort of output from the algebrizer tree but can’t seem to get my hands on that . .. if it’s even possible. If you’ve been down this road you will no doubt know the troubles I've faced and I'll be most sincerely grateful for you advice. Even just program’ably identifying the table will be great. I would prefer a T-SQL solution but perhaps that's not possible that's not possible. Thank you in advance and I will thank you again even for the slightest morsel. Ta Ten
query-planquery-optimisationquery-analyzer
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Care to post what your having a problem with and what the solution you hope to have looks like.
1 Like 1 ·
Hi Anthony I want to rewrite a SQL statement fully qualified. So 'select col1, col8r, colx from tab1 inner join tab2 on aa = bb' looks like this 'select tab1.col1, tab2.col8r, tab1.colx from tab1 inner join tab2 on tab1.aa = tab2.bb' Do you get my drift ?? Ta
0 Likes 0 ·
So what is the challenging part here?
1 Like 1 ·
G'day ... I need to be able to do that programatically for large SQL queries.If you know the names of the tables within a SQL statement you can work our the table names of all the columns in the statement because if there 2 instances of a column name in the tables it will already be fully qualified for the SQL to be valid, The tables names column names that are not fully qualified can be implicitly derived using the system tables. It boils down to working out the tables used in the statement. Literal strings and commented code can contain table names or reserved words like 'From' (which you need to use to find the table names) so you need to exclude them from analysis
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.