Select table names from queries

Question

Select table names from queries

Viewing 15 posts - 16 through 30 (of 47 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Eugene Elutin (8/30/2012)
Lowell (8/30/2012)
you could search for words that exist after "FROM" and "JOIN", right? ...
What about: INSERT, INTO, DELETE, TABLE (in possible truncate, create, alter or drop table statements).

good point Eugene!

also, to make it a little more bullet proof, you'd need to find/replace whitespace to be a single char; two spaces after FROM would hide a table; Tab(char(9) and of course the same thing for a CrLf;

all would need to be enhanced a bit...

not to mention finding tables inside comments...you need to strip comments out too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Geez, I go to bed, get up, and there's twenty new posts!

Anyhoo...

dwain.c (8/29/2012)
RBarryYoung (8/29/2012)
Unless the queries all strictly follow the same very simple format, this is going to fall somewhere between "Impossible" and "Incredibly Hard" to do in SQL.
If the queries are all truly valid (i.e., actually compilable, with tables and columns that actually exist in the database), then there is a way to do it the is merely "Very Difficult, Slow and Kludgy".
So Barry - what do you think of my suggestion?
Poll (select all that apply):
1. Impossible
2. Incredibly hard
3. Very Difficult
4. Slow
5. Kludgy
6. Clever
😀

I'll give you 6, but its still got most of 3, 4, and 5. And mostly what ChrisM said:

The biggest problem is that you've got to manually go through and add the "dbo." to all of the table names. If you can do that, why not just clip out the table names while you're there? Then you don't need an automated procedure at all.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Lowell (8/30/2012)
i get very few false positives when i go after my procedure and function definitions using that technique:
SELECT definition,T1.*,T2.* from sys.sql_modules
CROSS APPLY dbo.DelimitedSplit8K(definition,' ') T1
CROSS APPLY dbo.DelimitedSplit8K(definition,' ') T2
WHERE T1.ItemNumber + 1 = T2.ItemNumber
AND T1.Item IN('JOIN','FROM')

It's pretty good, Lowell, but it's highly dependent on the table naming conventions used and the the coding conventions used in the sql modules.

For instance, the presence or absence of brackets in the code. Or the presence of spaces in the table names (lots of reporting facilities in large applications produce these by the ton). Or the presence and use of Schema Names (and DB Names) in the table references (and frequently this is inconsistent in the code).

Etc., etc...

That's why I was so glum about the prospects for this request, the potential "gotcha's" really do go on and on.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Lowell (8/30/2012)
i get very few false positives when i go after my procedure and function definitions using that technique:
SELECT definition,T1.*,T2.* from sys.sql_modules
CROSS APPLY dbo.DelimitedSplit8K(definition,' ') T1
CROSS APPLY dbo.DelimitedSplit8K(definition,' ') T2
WHERE T1.ItemNumber + 1 = T2.ItemNumber
AND T1.Item IN('JOIN','FROM')

Hey! Don't forget APPLY! I use CROSS APPLY Tally all the time.

Good luck!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

...
not to mention finding tables inside comments...you need to strip comments out too.

Dwain solution will also suffer from having comments and also heavily depends on statement formatting

Try:

create table #temp1 (query nvarchar(max))

insert into #temp1

values (' SELECT * FROM dbo.table1')

, ('

/*

removed use of old table dbo.notexistanymore

*/

SELECT col1, col23

FROM dbo.table2

JOIN dbo.table3 b on a.col1 = b.col1

')

;WITH rCTE (tablename, query, n) AS (

SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))

,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1))

,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM #temp1

CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)

UNION ALL

SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))

,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1)), n

FROM rCTE

CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)

WHERE query <> '' AND SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' ')) <> '')

SELECT DISTINCT tablename

FROM rCTE

DROP TABLE #temp1

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

RBarryYoung (8/30/2012)
Geez, I go to bed, get up, and there's twenty new posts!
Anyhoo...
dwain.c (8/29/2012)
RBarryYoung (8/29/2012)
Unless the queries all strictly follow the same very simple format, this is going to fall somewhere between "Impossible" and "Incredibly Hard" to do in SQL.
If the queries are all truly valid (i.e., actually compilable, with tables and columns that actually exist in the database), then there is a way to do it the is merely "Very Difficult, Slow and Kludgy".
So Barry - what do you think of my suggestion?
Poll (select all that apply):
1. Impossible
2. Incredibly hard
3. Very Difficult
4. Slow
5. Kludgy
6. Clever
😀
I'll give you 6, but its still got most of 3, 4, and 5. And mostly what ChrisM said:
The biggest problem is that you've got to manually go through and add the "dbo." to all of the table names. If you can do that, why not just clip out the table names while you're there? Then you don't need an automated procedure at all.

I say the OP should make his developers do the grunt work and sit back and be a DBA!

Meanwhile, thanks for the 6! 😀

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Lowell SSC Guru Points: 323518 More actions · Answer 7

I love the peer review here; i didn't think of spaces in table names at all...

makes me think it's better to grab the execution plans instead, and parse the XML, since that would have parsed everything into objects.

Lowell (8/30/2012)
Eugene Elutin (8/30/2012)
Lowell (8/30/2012)
you could search for words that exist after "FROM" and "JOIN", right? ...
What about: INSERT, INTO, DELETE, TABLE (in possible truncate, create, alter or drop table statements).
good point Eugene!
also, to make it a little more bullet proof, you'd need to find/replace whitespace to be a single char; two spaces after FROM would hide a table; Tab(char(9) and of course the same thing for a CrLf;
all would need to be enhanced a bit...
not to mention finding tables inside comments...you need to strip comments out too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

Eugene Elutin (8/30/2012)
...
not to mention finding tables inside comments...you need to strip comments out too.
Dwain solution will also suffer from having comments and also heavily depends on statement formatting
Try:
create table #temp1 (query nvarchar(max))
insert into #temp1
values (' SELECT * FROM dbo.table1')
, ('
/*
removed use of old table dbo.notexistanymore
*/
SELECT col1, col23
FROM dbo.table2
JOIN dbo.table3 b on a.col1 = b.col1
')
;WITH rCTE (tablename, query, n) AS (
SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))
,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1))
,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM #temp1
CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)
UNION ALL
SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))
,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1)), n
FROM rCTE
CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)
WHERE query <> '' AND SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' ')) <> '')
SELECT DISTINCT tablename
FROM rCTE
DROP TABLE #temp1

Geesh! Many pitfalls.

Perhaps a good reason to go through (tediously) and clean up the data in this column.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

There is only one way to get it done with 100% accuracy.

In order to do so, you should be able to execute each query in a relevant database where it can be successfully compiled.

So, I'm not going to implement the logic, but I can outline the steps for implementing it:

1. Use a cursor to get through records

2. Wrap each statement in transaction which you will roll-back so it will not effect data

3. Execute each statement (if it's data modification ones, they will be rolled back as per #2)

4. Grab the execution plan XML

5. Parse it to find all tables as it will contain a lot of the following:

Database="[Whatever]" Schema="[Whatever]" Table="[Whatever]"

In this case you don't need to care about comments, formatting or name qualifiers.

Good luck!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT

m.[Definition],

d.TABLE_NAME

FROM sys.sql_modules m

OUTER APPLY (

SELECT TABLE_NAME

FROM YourDatabase.INFORMATION_SCHEMA.TABLES t

WHERE m.definition LIKE '%'+t.TABLE_NAME+'%'

GROUP BY TABLE_NAME

) d

Edit: changed db name

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

ChrisM@Work (8/30/2012)
SELECT m.[Definition], d.TABLE_NAME FROM sys.sql_modules m OUTER APPLY ( SELECT TABLE_NAME FROM YourDatabase.INFORMATION_SCHEMA.TABLES t WHERE m.definition LIKE '%'+t.TABLE_NAME+'%' GROUP BY TABLE_NAME
) d
Edit: changed db name

A man of few words! I love it. The spirit of a true code-talker.

Trouble is, I can't hear nuthin' over the whisperin' of da wind. :w00t:

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

dwain.c (8/30/2012)
ChrisM@Work (8/30/2012)
SELECT m.[Definition], d.TABLE_NAME FROM sys.sql_modules m OUTER APPLY ( SELECT TABLE_NAME FROM YourDatabase.INFORMATION_SCHEMA.TABLES t WHERE m.definition LIKE '%'+t.TABLE_NAME+'%' GROUP BY TABLE_NAME
) d
Edit: changed db name
A man of few words! I love it. The spirit of a true code-talker.
Trouble is, I can't hear nuthin' over the whisperin' of da wind. :w00t:

Oh...sorry about that. I made a paella last night, too much garlic 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Right, this second wave of methods is similar to what I had in mind when I asked the OP "If the queries are all truly valid (i.e., actually compilable, with tables and columns that actually exist in the database), then ..."

Because if they are, then there are three different ways to approach it:

1. Scan the text for the known table names.

2. Test execute it, grab the query plan, and parse the XML, or

3. Make each query into a stored procedure, read the system dependency tables, drop the procedure, go to the next query...

Of these, #1 is the easiest and the only one that I have gone all the way through and implemented as a "90% solution". And it does work pretty well (though it's slow), better than 90% of the time.

You just have to hope that the developers didn't do something insane like name a table "Text" or "getdate". This has happened to me at least twice and it tends to throw the whole thing in the crapper effort-wise.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

The real problem here though is that the OP never confirmed if the table names actually exist in the database. None of these three tricks will work if they do not.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

RBarryYoung (8/30/2012)
The real problem here though is that the OP never confirmed if the table names actually exist in the database. None of these three tricks will work if they do not.

Quite so. You could pull all of the tablenames for the instance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden