1

I have several nested procedures. A temp table is being created in the 1st one and later used at several places. Currently I have created a index on the temp table, but need to verify as I am still seeing it to run for long time.

I tried to replicate my problem following stored proc

create proc ProcSp
as
    sp_help #tmpCheck
    go
END
go

I get error "Incorrect Syntax near sp_help".

Please let me know how would we be able to call sp_help on a temp table inside a proc?

Edit

Is there any way to just display indexes on any table inside a proc?

Community
  • 1
  • 1
mtk
  • 13,221
  • 16
  • 72
  • 112

1 Answers1

0

sp_help is a stored procedure so like all stored procs in needs to be called via exec

However in this case if you want to confirm the index has been created then it is better to check it when the index is created. Do this by looking at the @@error variable just after you create the index, if it is zero then the index is correct

e.g.

create index idx_temp on #tmpCheck(field)
set @err_code = @@error
if @err_code <> 0
begin
   print @err_code
   rollback ......
end
mmmmmm
  • 32,227
  • 27
  • 88
  • 117
  • I am including my temp table using the `:r` syntax. I have added the create index line in the same file. Not sure where to look for this print statement. Can you please tell me..? – mtk Nov 21 '12 at 10:53
  • @mtk - easier to debug if in one file - if just including that then put the test in the file you create the index in – mmmmmm Nov 21 '12 at 11:00