Search for Global Temp tables in multiple stored procedures

Question

Search for Global Temp tables in multiple stored procedures

Lord Slaagh

SSCertifiable

Points: 5745
More actions
October 20, 2022 at 6:07 pm
Go to Answer
#4102127

Hello SSC,
I hope everyone is doing well!
I inherited an SSIS project that executes several stored procedures. I see global temp tables in most of the stored procedures but I cannot find the source that is building them. Does anyone have a query that can select all stored procs that contain global temp tables?
I do not see a sys.stored_procedures table. Maybe it's in a DMV?
BTW: I disagree with Global Temp tables. I do not see the need. I suppose you can use them for lookup tables and things of that nature for multiple projects, but even then, you are susceptible for the data to be inaccurate. I found that local temp tables are more than enough, especially since we have CTE's as well.
I understand why SQL has them, I just never had to use them.
Any help would be appreciated!
Dave
The are no problems, only solutions. --John Lennon
ScottPletcher

SSC Guru

Points: 101123
More actions
October 20, 2022 at 6:53 pm
Answer
#4102157
```
SELECT OBJECT_NAME(object_id) AS object_name
    SUBSTRING(definition, CHARINDEX('##', definition) - 20, 200) AS definition_first_match,
    definition
FROM sys.sql_modules
WHERE definition LIKE N'%##%'
ORDER BY 1
```
Edit: Sorry, I should have been clearer with code. I never expected anyone to do a non-computer search for the string.
- This reply was modified 2 years, 2 months ago by ScottPletcher.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 9 posts - 1 through 8 (of 8 total)

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

ScottPletcher SSC Guru Points: 101123 More actions · Answer 1

Search for '##' in the text of the proc. The only real reason that string should be in there is to reference a global temp table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lord Slaagh SSCertifiable Points: 5745 More actions · Answer 2

Thank you for your response, but I am looking for a query that searches all stored procedures for the '##' sign. I can't manually check all of them we have over 500 procs.

The are no problems, only solutions. --John Lennon

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

Lord Slaagh wrote:

Thank you for your response, but I am looking for a query that searches all stored procedures for the '##' sign. I can't manually check all of them we have over 500 procs.

well.... you can (and should) have them already on a Visual Studio solution where the search would be fast and would give you ALL occurrences of it.

maybe its time to do it. - and put them on a source control repository

Lord Slaagh SSCertifiable Points: 5745 More actions · Answer 4

Fredrico,

Are you suggesting searching in Management Studio or Visual Studio. I am not sure if I have searched the way you are suggesting.

Thank you so much for your response!

Dave

The are no problems, only solutions. --John Lennon

Lord Slaagh SSCertifiable Points: 5745 More actions · Answer 5

Thank you Scott!!

This is exactly what I need.

The are no problems, only solutions. --John Lennon

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

frederico_fonseca wrote:

Lord Slaagh wrote:
Thank you for your response, but I am looking for a query that searches all stored procedures for the '##' sign. I can't manually check all of them we have over 500 procs.
well.... you can (and should) have them already on a Visual Studio solution where the search would be fast and would give you ALL occurrences of it.
maybe its time to do it. - and put them on a source control repository

I totally agree with the "Source Control" suggestion but, even if I used VS, I'd still use a search on the definition, just to be sure. Been burned too many times by :features". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

And if you want to search all databases at the same time:

exec sp_MSforeachdb 'use [?] select ''?'' as dbName, name as ''SP_Name'', obj.type, 
substring(definition,CHARINDEX(''##'', definition)-45,150) as ''Code_Snippet'' 
from sys.sql_modules com
join sysobjects obj on com.object_id = obj.id
where (definition like ''%##%'')
and ''[?]''  <> ''Omit_This_DB''
order by dbName, name'

This reply was modified 2 years, 2 months ago by homebrew01.