| Author |
Topic |
|
sonysunny
Starting Member
6 Posts |
 Posted - 2008-03-05 : 04:10:04
|
| hai,How can we drop a global temporary table which is ceated by a stored procedure.plz help.thanks... |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-05 : 04:10:29
|
| [code]Drop Table ##Temp1[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sonysunny
Starting Member
6 Posts |
Posted - 2008-03-05 : 04:12:33
|
| thanks harsh, Before drop the table i want to check whether it exists or not.How can we do that. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-05 : 04:15:27
|
| [code]If object_id('tempdb..##temp') is not nullprint 'Table Exists'elseprint 'Table not Exists'[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
sonysunny
Starting Member
6 Posts |
Posted - 2008-03-05 : 04:24:01
|
| thanks harsh. Its working fine. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-03-05 : 04:45:01
|
| BUTDoes this mean that a temp table is in any way temporary?Please straighten out these things for me.I thought the very point of this being temporary was that it would be automatically dropped if not used after certain time (which would be a databaase setting somewhere). I've learn that global is ##table but how does this differ from #table? And what is the difference at all from perm / temp? Are they in memory or on disk...? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-05 : 04:53:48
|
| Have you tried reading about temp tables in SQL Server help.Yes. Temp Table is dropped automatically when the connection in which it was created is closed, but it is good practice to drop it manually once you are done with it to avoid contention on tempdb resources.Global temp table is visible across connections while temp table is accessible only for the connection in which it is created. Both these are created in tempdb database.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-03-05 : 05:06:38
|
| How can I check properties of tables not in Enterprise Manager 2k but in queries & look at statistics, such as user table sizes and sys tables - master, tempdb, model and msdb? I likedselect * from sys.<object types> in 2005I know procedures, tables, views, objects, colums, triggers, and many others. Brilliantbut now in 2k I have select * from sysobjects, syscolumns, sysdatabases so I'm told but this last one doesn't work. Are there any others?Thanks H |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-05 : 05:44:31
|
| How is this related to original question?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-03-05 : 23:02:35
|
quote:
but it is good practice to drop it manually once you are done with it to avoid contention on tempdb resources.
??? Ok, harsh... you need to explain that one. If it's dropped automatically, why would there be a contention on TempDB resources?????--Jeff Moden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-07 : 10:07:25
|
quote:
Originally posted by Jeff Moden
quote:
but it is good practice to drop it manually once you are done with it to avoid contention on tempdb resources.
??? Ok, harsh... you need to explain that one. If it's dropped automatically, why would there be a contention on TempDB resources?????--Jeff Moden
Because the temp table is alive till the time the connection open and if the size of temp table is significant enough, it can very well slow down overall tempdb performance. Besides, I don't see any point in keeping temp table unnecessarily hanging around when you don't need them anymore, especially global temp tables.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-03-08 : 18:28:39
|
| Ok... just to fill in what you're implying but not coming right out and saying... I can see that if you get done with the temp table half way through a proc...--Jeff Moden |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-08 : 19:09:02
|
| @sonysunny:what are you doing that requires a global temp table? there are very few scenarios that really require that._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
how to drop a temporary table