drop global temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

drop global temp table

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2003-06-13 : 00:18:09

Hi there,

I want to check for existance of a global temp table and if exists drop it. The code below does not find or drop this table even though it does exist:

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[##TempPostCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tempdb].[dbo].[##TempPostCodes]

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'TempPost_ind')
DROP INDEX ##TempPostCodes.TempPost_ind

CREATE TABLE ##TempPostCodes(
TempPostCodeID int IDENTITY (1,1) not null ,
DistributorID int,
PostCode Char(10),
Locality varchar(40),
Latitude decimal,
Longitude decimal,
CalcLat decimal,
CalcLong decimal,
CalcDiff decimal)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-13 : 01:26:31

Matt,

I don't think temp tables can participate in OBJECTPROPERTY expressions..

When dropping global tables you don't need to specify the tempdb table or owner...although it shouldn't matter.

And finally when using OBJECT_ID you must specify the name using at least a 3 part naming convention..

It should look like this...


if exists (select * from tempdb.dbo.sysobjects where id = OBJECT_ID(N'tempdb.dbo.##TempPostCodes')) 
BEGIN
      DROP TABLE ##TempPostCodes
	print 'Table dropped'
END
ELSE
	print 'Not dropped'

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/13/2003 01:27:29

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-13 : 08:16:50

You can shorten David's code to:

IF OBJECT_ID(N'tempdb.dbo.##TempPostCodes')>0 DROP TABLE ##TempPostCodes

This works on any object type BTW, tables (regular, temp, global temp), views, sproc's, rules, etc., but you have to match DROP <whatever> to match it's object type (table, view, procedure)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources