|
|
Posted by comp_databases_ms-sqlserver on 10/25/06 22:41
This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro
--***********************************
use pubs
go
CREATE PROCEDURE Test @percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor
--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)
--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1
go
set showplan_Text ON
go
EXEC Test @percentage = 100
GO
set showplan_Text OFF
go
**************************************
I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.
I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.
Regards
Navigation:
[Reply to this message]
|