October 22, 2014 at 12:47 pm
Does anyone know a way to find all stored procedures that use declared or temp tables, i.e
Declare @temptable TABLE as....
Create table #temptable
Thanks!
October 22, 2014 at 1:40 pm
Quick suggestion, search the sys.all_sql_modules definition
😎
SELECT
OBJECT_NAME(MO.object_id) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(MO.object_id) AS OBJ_SCHEMA
,MO.definition
FROM sys.all_sql_modules MO
WHERE MO.object_id > 0
AND MO.definition LIKE '% TABLE %'
October 23, 2014 at 1:23 am
d short (10/22/2014)
Does anyone know a way to find all stored procedures that use declared or temp tables, i.eDeclare @temptable TABLE as....
Create table #temptable
Thanks!
If you have your databases scripted into source control (which I'm sure you do :-)), you may find it easier to do a regex search across the source files using a tool like FileSeek[/url]. The free version is adequate for your needs, and it's fast.
I can help with the necessary regular expression if you're new to the genre.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2014 at 4:12 am
You could try SQL Search[/url] from Red Gate. It's a free tool that should find those references pretty easily.
Mandatory disclaimer (although I'm not sure why for a free tool): I work for Red Gate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2014 at 4:37 am
Grant Fritchey (10/23/2014)
You could try SQL Search[/url] from Red Gate. It's a free tool that should find those references pretty easily.Mandatory disclaimer (although I'm not sure why for a free tool): I work for Red Gate.
Perhaps. But this is better 🙂
For a table variable, use regular expression declare\s+@\S+\s+table\s+
For a temp table, use regular expression create\s+table\s+#\S+
'better', because using a regular expression allows you to define the structure of what you are searching for more specifically than is possible in T-SQL and (as far as I know) SQL Search.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2014 at 5:41 am
yeah, it doesn't do regular expressions. Wild cards, AND/OR and NOT are supported though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2014 at 10:41 am
For temp tables, you must also consider:
INTO #new_temp_table
rather than just CREATE.
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".
October 23, 2014 at 10:47 am
Using a regEx search CLR (we already had one inhouse) does the job. Thanks for pointing me in the right direction.
October 23, 2014 at 11:47 am
ScottPletcher (10/23/2014)
For temp tables, you must also consider:INTO #new_temp_table
rather than just CREATE.
Good point! I try to stop people doing that in production code, but a few get through the net.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply