Select table names from queries
-
RBarryYoung (8/30/2012)
Geez, I go to bed, get up, and there's twenty new posts!...
Have you checked the date today Barry? 😛
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
While trying to come up with solution I've found some issue of getting the query plan for simple query.
It all to do with automatic query parametrisation. Check this out:
create table dbo._tt_t1 (c1 int)
go
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
EXEC ('
BEGIN TRANSACTION AnalyseSql;
insert _tt_t1 values (2)
;ROLLBACK TRANSACTION AnalyseSql;
')
SELECT qp.query_plan
,cp.plan_handle
,st.[TEXT]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan (cp.plan_handle,0,-1) AS tp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
-- WHERE st.[TEXT] LIKE 'BEGIN TRANSACTION AnalyseSql%'
You can see that I've tried to filter by TEXT which starts from 'BEGIN TRANSACTION AnalyseSql%'.
But the query text has no reference to transaction at all! :ermm:
Will try to get around it somehow....
-
It did took me a while.
It may not be a bulletproof way, but much better than others I could think of so far...
Enjoy:
-- setup
create table dbo._tt_t1 (c1 int)
go
create table dbo._tt_t2 (c1 int)
go
create table dbo._tt_t22 (c1 int)
go
create schema other
go
create table other._tt_t3 (c1 int)
go
create table other._tt_t33 (c1 int)
go
create table #tSqls (id int, [sql] varchar(max))
insert #tSqls select 1, 'insert _tt_t1 values (2)'
insert #tSqls select 2, 'select * from _tt_t1 '
insert #tSqls select 3,
'
-- that is just a comment dvo.NoSuchTable
SELECT *
FROM dbo._tt_t2
JOIN _tt_t1 ON 1 = 1'
insert #tSqls select 4, 'delete other._tt_t3 WHERE 1=2'
insert #tSqls select 5, '
--MULTIPLE STATEMENTS
insert _tt_t22 select 1
select * from other._tt_t33
'
-- The business :-) :
declare @sql varchar(max)
create table #qp (qp xml)
declare c_t cursor
for select [sql] from #tSqls
open c_t
fetch next from c_t into @sql
while @@fetch_status = 0
begin
IF OBJECT_ID('dbo._tmp_analysesql') IS NOT NULL DROP PROCEDURE dbo._tmp_analysesql
SET @sql =
'Create proc dbo._tmp_analysesql
as
begin
begin transaction AnalyseSql
' + @sql + '
rollback transaction AnalyseSql
end
'
EXEC (@sql)
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
EXEC dbo._tmp_analysesql
INSERT #qp
SELECT decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
IF OBJECT_ID('dbo._tmp_analysesql') IS NOT NULL DROP PROCEDURE dbo._tmp_analysesql
fetch next from c_t into @sql
end
close c_t
deallocate c_t
declare @qpx xml
declare @usedtables table (tablename varchar(1000))
declare a_p cursor for select qp from #qp
open a_p
fetch next from a_p into @qpx
while @@fetch_status = 0
begin
INSERT @usedtables
SELECT Y.x.value('@Schema', 'varchar(500)') + '.' + Y.x.value('@Table', 'varchar(500)')
FROM @qpx.nodes('//*[@Schema and @Table]') Y(x)
fetch next from a_p into @qpx
end
close a_p
deallocate a_p
drop table #tSqls
drop table #qp
-- now display all used tables
select distinct tablename from @usedtables
-- clean up
drop table dbo._tt_t1
go
drop table dbo._tt_t2
go
drop table dbo._tt_t22
go
drop table other._tt_t3
go
drop table other._tt_t33
go
drop schema other
go
Someone will ask if the stored proc creation is really necessary. I'm not 100% sure. But I couldn't find the nicer way to grab relevant query plans. SQL parametrizes simple insert queries straight away!
However, it may be possible somehow in other way.
Yeah. It will only work if queries are executable. But from another point of view, if they are not, they shouldn't be even called queries until fixed as their text may contain any rubbish...
-
Great job Eugene, thanks!:Wow:
There is an exception to every rule, except this one...
-
I don't want anyone to think I'm getting defensive about my approach - I am not.
But I've taken Eugene's comments about comments 🙂 into account and also eliminated VIEWs, SPs and pretty much anything else that can be qualified by schema (even allowing for alternate schemas) that is not a TABLE in this version.
DECLARE @Catalog VARCHAR(50), @Schema VARCHAR(20)
SELECT @Catalog = 'YourCatalogName', @Schema = 'dbo'
;WITH rCTE (tablename, query, n) AS (
SELECT REPLACE(SUBSTRING(str3, 1, CHARINDEX(' ', str3 + ' ')), CHAR(13), '')
,SUBSTRING(str3, CHARINDEX(' ', str3 + ' '), 1+LEN(str3))
,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM #temp1
-- Eliminate an /* */ comments
CROSS APPLY (
SELECT REPLACE(REPLACE(query, '/*', '--'), '*/', CHAR(13)) + CHAR(13)
) a (str1)
CROSS APPLY (
SELECT CASE WHEN CHARINDEX('--', str1) > 0
THEN STUFF(str1
,CHARINDEX('--', str1)
,1 + CHARINDEX(CHAR(13), str1) - CHARINDEX('--', str1)
,'')
ELSE str1 END
) b (str2)
CROSS APPLY (
SELECT SUBSTRING(str2, CHARINDEX(@Schema + '.', str2), LEN(str2))
) c (str3)
UNION ALL
SELECT REPLACE(SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' ')), CHAR(13), '')
,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1)), n
FROM rCTE
CROSS APPLY (
SELECT CASE WHEN CHARINDEX('--', query) > 0
THEN STUFF(query
,CHARINDEX('--', query)
,1+ CHARINDEX(CHAR(13), query) - CHARINDEX('--', query)
,'')
ELSE query END
) a (str2)
CROSS APPLY (
SELECT SUBSTRING(str2, CHARINDEX(@Schema + '.', str2), LEN(str2))
) b (str1)
WHERE query <> '' AND SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' ')) <> '')
SELECT DISTINCT tablename
FROM rCTE a
INNER JOIN information_schema.tables b
ON TABLE_CATALOG = @Catalog AND
TABLE_SCHEMA = @Schema AND
TABLE_TYPE = 'BASE TABLE' AND
REPLACE(RTRIM(tablename), @Schema + '.', '') = b.TABLE_NAME
With a little work, you could probably even get it to parse out a suffixed catalog name too. Perhaps I'll leave that for another day.
I just hope I got the line terminator character (CHAR(13)?) right (didn't look it up).
Just thought I'd throw this out there.
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 -
dwain.c (8/30/2012)
...I just hope I got the line terminator character (CHAR(13)?) right (didn't look it up).
The line terminator can be any of CHAR(13), or CHAR(10) or CHAR(13)+CHAR(10).
[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 (8/30/2012)
dwain.c (8/30/2012)
...I just hope I got the line terminator character (CHAR(13)?) right (didn't look it up).
The line terminator can be any of CHAR(13), or CHAR(10) or CHAR(13)+CHAR(10).
Ugh! Well that does add a slight bit of complexity. But still it can be handled.
Just playing around for the fun of it. Others seem to have proposed more effective (if complex) alternatives that I simply don't have the capability to attempt (yet).
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 -
dwain.c (8/30/2012)
I don't want anyone to think I'm getting defensive about my approach - I am not.But I've taken Eugene's comments about comments 🙂 into account and also eliminated VIEWs, SPs and pretty much anything else that can be qualified by schema (even allowing for alternate schemas) that is not a TABLE in this version.
...
Except my approach works for tables with or without any number of name qualifiers, and will also work across any accessible database (there is a Database attribute can be taken from the same node of execution plan xml). I think also, that I can achieve the same without wrapping code into sps.
Also, youdo only remove simple cases of comments. What about case for multi-line comment:
/*
Comment line 1
Comment line 2
*/
and if you code for the above, please make sure that the next scenario is not taken as a commented out code
-- /* Removed code
SELECT ....
INSERT ....
-- */ -- end of removed code
-
Here the version which doesn't create stored proc.
-- setup
create table dbo._tt_t1 (c1 int)
go
create table dbo._tt_t2 (c1 int)
go
create table dbo._tt_t22 (c1 int)
go
create schema other
go
create table other._tt_t3 (c1 int)
go
create table other._tt_t33 (c1 int)
go
create table #tSqls (id int, [sql] varchar(max))
insert #tSqls select 1, 'insert _tt_t1 values (2)'
insert #tSqls select 2, 'select * from _tt_t1 '
insert #tSqls select 3,
'
-- that is just a comment dvo.NoSuchTable
SELECT *
FROM dbo._tt_t2
JOIN _tt_t1 ON 1 = 1'
insert #tSqls select 4, 'delete other._tt_t3 WHERE 1=2'
insert #tSqls select 5, '
--MULTIPLE STATEMENTS
insert _tt_t22 select 1
select * from other._tt_t33
'
-- The business :-) :
declare @sql nvarchar(max)
create table #qp (qp xml)
declare c_t cursor
for select [sql] from #tSqls
open c_t
fetch next from c_t into @sql
while @@fetch_status = 0
begin
SET @sql =
'SET NOCOUNT ON
begin transaction AnalyseSql;
' + @sql + '
;rollback transaction AnalyseSql
'
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
EXEC (@sql)
INSERT #qp
SELECT decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
fetch next from c_t into @sql
end
close c_t
deallocate c_t
declare @qpx xml
declare @usedtables table (tablename varchar(1000))
declare a_p cursor for select qp from #qp
open a_p
fetch next from a_p into @qpx
while @@fetch_status = 0
begin
INSERT @usedtables
SELECT Y.x.value('@Schema', 'varchar(500)') + '.' + Y.x.value('@Table', 'varchar(500)')
FROM @qpx.nodes('//*[@Schema and @Table]') Y(x)
fetch next from a_p into @qpx
end
close a_p
deallocate a_p
drop table #tSqls
drop table #qp
-- now display all used tables
select distinct tablename from @usedtables
-- clean up
drop table dbo._tt_t1
go
drop table dbo._tt_t2
go
drop table dbo._tt_t22
go
drop table other._tt_t3
go
drop table other._tt_t33
go
drop schema other
go
I think to make it really useful, it should be wrapped into non-SQL application, which will ignore all resultsets except the final one. I couldn't find the way to generate readable query plan without execution of SQL itself. Setting NOEXEC ON produces no cashed plan, FMTONLY works only for queries which produces resultsets (all data modification statements ignored).
Any other ideas?
-
Eugene,
Thanks for the suggestions on the comments. I'll run them through and try to improve it (I have some other ideas for it anyway).
One question though. If your method requires that you execute each query to capture the query plan, and you have 1000 of these SQL statements to parse table names out of and 50% of those run for 3 minutes, doesn't that mean your process is going to take like 1500 minutes to run?
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 -
dwain.c (8/31/2012)
Eugene,Thanks for the suggestions on the comments. I'll run them through and try to improve it (I have some other ideas for it anyway).
One question though. If your method requires that you execute each query to capture the query plan, and you have 1000 of these SQL statements to parse table names out of and 50% of those run for 3 minutes, doesn't that mean your process is going to take like 1500 minutes to run?
There is no point even to ask this question as you know the answer: Yes it will, for conditions you've specified.
The best way would be to generate estimated execution plans and analyse them, but as far as I'm aware it's impossible using T-SQL.
Anyway, if I would want to parse sql to analyse it I would never use T-SQL for this. It's a wrong tool for the purpose.
-
Eugene Elutin (8/31/2012)
dwain.c (8/31/2012)
Eugene,Thanks for the suggestions on the comments. I'll run them through and try to improve it (I have some other ideas for it anyway).
One question though. If your method requires that you execute each query to capture the query plan, and you have 1000 of these SQL statements to parse table names out of and 50% of those run for 3 minutes, doesn't that mean your process is going to take like 1500 minutes to run?
There is no point even to ask this question as you know the answer: Yes it will, for conditions you've specified.
The best way would be to generate estimated execution plans and analyse them, but as far as I'm aware it's impossible using T-SQL.
Anyway, if I would want to parse sql to analyse it I would never use T-SQL for this. It's a wrong tool for the purpose.
Whoa dude! Didn't mean to get your hackles up there. I thought perhaps I was missing something.
I need to commend you on the depth of knowledge you've demonstrated by coming up with this. As I stated already, it is way beyond me, and I've learned quite a bit about what can be extracted from SQL's bowels just reading it. Not to mention your insistence on accuracy and completeness. Very commendable as well.
I'm just thinking that maybe an 80% solution is enough in some cases. Not saying mine is there yet. But I think I can get it there.
And as to T-SQL not being the right tool, I smell a challenge, and you know I love a good challenge! Whatever tool you use, if it's going to render results that indicate what are valid objects or not, it will need to plunge deeply into the entrails of the SQL sys tables anyway. So why not SQL?
And out of curiosity, what tool would you propose to use?
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 -
...
And as to T-SQL not being the right tool, I smell a challenge, and you know I love a good challenge! Whatever tool you use, if it's going to render results that indicate what are valid objects or not, it will need to plunge deeply into the entrails of the SQL sys tables anyway. So why not SQL?
And out of curiosity, what tool would you propose to use?
T-SQL is not designed and it is not right tool for text parsing. You can use it for simple things, but not for complex text analyse. There are special tools/languages which are designed for this particular task. I would use the one I'm more familiar with and it would be c#. However, there are even better ones which do exactly that work.
If you are talking about 80% of cases I think both methods would be good enough. I don't think that many dynamic sql's stored in table for execution would contain complex comment structures or will be in their thousands (can image about maintenance of such thing?)
If you really like the challenge, this one for you:
draw Mona Lisa using T-SQL (I remember I had seen assembler application which would print out this one using characters and digits on old alphanumeric printing devices for IBM Mainframe lot)
Good luck.
-
Eugene Elutin (8/31/2012)
...draw Mona Lisa using T-SQL...Okay.
SELECT *FROM (
SELECT '......................' UNION ALL
SELECT '......................' UNION ALL
SELECT '......########........' UNION ALL
SELECT '.....##...#.###.......' UNION ALL
SELECT '.....#..#...###.......' UNION ALL
SELECT '.....#..##..###.......' UNION ALL
SELECT '.....##.##.####.......' UNION ALL
SELECT '.....##....#####......' UNION ALL
SELECT '.....#.....######.....' UNION ALL
SELECT '....##.....#######....' UNION ALL
SELECT '....###############...' UNION ALL
SELECT '...#################..' UNION ALL
SELECT '..###################.' UNION ALL
SELECT '#####...#############.' UNION ALL
SELECT '####..##..############' UNION ALL
SELECT '######################' ) d (BigPixels)
Results:
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Eugene Elutin (8/31/2012)
If you really like the challenge, this one for you:draw Mona Lisa using T-SQL (I remember I had seen assembler application which would print out this one using characters and digits on old alphanumeric printing devices for IBM Mainframe lot)
Good luck.
Nah! It's been done already.
Doubt I can beat Chris' solution for speed.
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
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply