Click here to Skip to main content
15,867,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have some Stored Procedure. Where multiple times some global tables are created (start with ##). I want to list out the table Names. For example -

1-
SQL
create table ##customer
(
Customerid bigint
)


2-
SQL
if(OBJECT_ID('tempdb..##Department')) is null
begin
create  table    ##Department
(
Departmentid bigint
)
end


3-
SELECT * FROM ##customer

I want to list out the Temp Table names, which are created and which are used as a select query. Please help. 1st priority using Sql Query. If you do not have idea in sql, then using C# . I will pass the Stored procedure content to C# method.
Posted
Updated 4-Dec-14 8:38am
v2
Comments
Maciej Los 4-Dec-14 14:39pm    
Help what? I really do not understand you ;(
Upadhyay Praveen 4-Dec-14 14:46pm    
Whatever Query example i have mentioned in question. Lets assume that is inside a Stored Procedure. I want to extract all the Temp tables which starts with ## to be listed out. In this case - ##Customer, ##department. In real scenario I have 1000+ Stored procedure, I want to get the name and rename to the temp tables inside the sp.

Provided you have access... Start here:

SQL
SELECT name
, definition
FROM sys.objects A
INNER JOIN sys.sql_modules B
ON A.object_id=B.object_id
WHERE type='p'
AND definition LIKE '%##%'


It should get you the names and definitions of the procedures that use a global temp table, that'll narrow things down a bit.
 
Share this answer
 
Comments
Upadhyay Praveen 4-Dec-14 14:51pm    
Yes, using this query i have listed down the Stored procedures which contains global table. Now next step i want to know the global table name.
PIEBALDconsult 4-Dec-14 14:55pm    
Try a Regular Expression?
Upadhyay Praveen 4-Dec-14 15:03pm    
Yes, that is why i am thinking to find out using c#. I am trying with expression . But still did not get. It should start with ##, end with ' (single quotes) or space or enter .
PIEBALDconsult 4-Dec-14 15:06pm    
##\w+
Upadhyay Praveen 5-Dec-14 12:30pm    
Thanks.
You can query the temp tables like this:
SQL
SELECT * FROM tempdb.sys.tables
WHERE name LIKE '##%'

But you want also see their usage, if I have understood you right. Well, as there is no reference kept you actually can't tell at any point of time all statement s using one specific table. You can however trace this, if you really need. Still that's not easy - consult the code of this application: https://expressprofiler.codeplex.com/SourceControl/latest[^]
 
Share this answer
 
Comments
PIEBALDconsult 4-Dec-14 15:13pm    
I suppose that won't find temp tables that are created and dropped by procedures.
Zoltán Zörgő 4-Dec-14 15:15pm    
Only during their life.
But I don't get your final point: why do you want all this?
PIEBALDconsult 4-Dec-14 15:17pm    
I don't.
Upadhyay Praveen 4-Dec-14 15:20pm    
i want to get the table name, and then will renaming. If i have few SPs then i can do manually. I have 1000+Sps. I already have renaming logic, just want to know the temp table names which starts with ##.
PIEBALDconsult 4-Dec-14 15:26pm    
What's in a name?
Thanks friends. I have done this using Regular expression in c#, and called those methods in sql by using CLR.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900