-1

I want to get the count of how many temporary tables are used in a stored procedure.

Thanks in advance

neer
  • 4,031
  • 6
  • 20
  • 34
Syed Aqib
  • 51
  • 2
  • 5
  • Mabe this post is helpful to you: http://stackoverflow.com/questions/7075483/is-there-a-way-to-get-a-list-of-all-current-temporary-tables-in-sql-server – Carsten Massmann Sep 20 '16 at 05:23
  • 1
    just search for `#` in you stored procedure. – Mr. K Sep 20 '16 at 05:31
  • I wanted through a query to search, which includes 3000 lines of code in a SP. – Syed Aqib Sep 20 '16 at 06:30
  • @RohitKumar : That's not a good idea. Same temp table might used multiple times. For example, Create, alter, truncate, drop ....... with same temp table. – Ullas Sep 20 '16 at 06:42

2 Answers2

1

Here is an algorithm to make this work for you:

  1. Get the text of the stored proc from Information_Schema.Routines from the routine_definition column

Declare @Def VarChar(Max) = '' Select @Def = @Def + Routine_Definition From Information_Schema.Routines

  1. Split text of the routine by white space (one word on each row)

Read this great answer:

Turning a Comma Separated string into individual rows

  1. Count / Display all distinct words that start with #

Afterthought 1:

Now if you have [#One Two Three] and [#One Two Three Four] it will show up as #One only, so you would have to use some TSQL trickery to strip spaces between square brackets.

Afterthought 2:

Will you have to distinguish between #Temp and ##Temp tables?

Community
  • 1
  • 1
Raj More
  • 47,048
  • 33
  • 131
  • 198
0

I don't really get why you want to do this but the following query might help.

This just counts the number of hashes that appear a stored procedures definition which is stored in the system view 'sys.sql_modules'.

SELECT
    LEN(m.[definition]) - 
    LEN(REPLACE(m.[definition],'#','')) AS 'CountOf#Characters'
FROM
    sys.sql_modules m
    JOIN sys.procedures p
        ON m.[object_id] = p.[object_id]
WHERE
    p.[name] = 'Testing' -- <<the name of your stored procedure

Of course this assumes the procedure only contains CREATE TABLE #Table1 style statements. If hashes are used to drop the tables or even used in code comments you will need to take this into consideration with wrapping replace functions.

Paul Andrew
  • 3,233
  • 2
  • 17
  • 37