Does the Table exist *solved!*

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

Transact-SQL (2000)

Does the Table exist *solved!*

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 12:58:49

Will someone point me in the right direction?

I have a sp that creates temp tables. I need to verify that these tables do NOT exist before it runs. If they do, then I want to delete them.

I did a search on this site for info but didn't find anything that's relevant.

Any ideas?

Thanks in advance!
Teresa

"Happiness is found along the way;
not at the end of the road."

Edited by - tj on 03/06/2002 09:10:20

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 13:22:10

Temp tables are stored in the sysobjects table in tempdb so you have to explicitly look for it there (or so I think). You have to use like and the % sign after the name of the temp table you are looking for because SQL server appends a long line of underscores and a number so multiple users on the same server can create multiple temp tables of the same name. As a result, be careful with this one.

if exists (select * from tempdb.dbo.sysobjects where name like '#FOO%')
DROP TABLE #FOO
ELSE
CREATE TABLE #FOO

Edited by - JamesT on 03/05/2002 13:24:39

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 13:29:32

quote:

#FOO

Is the # sign a reference to something or just part of the table's name?

Thanks!

"Happiness is found along the way;
not at the end of the road."

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 13:37:52

I tried it both ways and it is not finding the table.

I didn't express myself very well! I'll get the hang of this stuff yet! :o)

I created TABLES (that are used as temp tables) to run another sp on.
These are what I need to check for.

Sorry about that!

Thanks!

"Happiness is found along the way;
not at the end of the road."

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 13:40:56

This code works though!

quote:

if exists (select * from sysobjects where name like 'tmpqik%')
DROP TABLE tmpqik

Thanks for the clue!

Teresa

"Happiness is found along the way;
not at the end of the road."

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 13:55:43

When you said temporary tables I thought you really meant temporary tables. Temporary tables are prefixed with a '#' sign. There is a better way to look for tables and either drop or create them when processing. To see how to do this look in help for Generating SQL scripts from objects.

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 14:30:45

quote:

When you said temporary tables I thought you really meant temporary tables. Temporary tables are prefixed with a '#' sign. There is a better way to look for tables and either drop or create them when processing. To see how to do this look in help for Generating SQL scripts from objects.

At the risk of appearing completely unintelligent... look where? I didn't see it in the TSQL help in Query Analyzer or Enterprise Manager help?

Thanks again!
Teresa

"Happiness is found along the way;
not at the end of the road."

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 15:02:11

Sorry, previous post was while I was at home and didn't have enterprise manager available. So, to script a table:

1. Right click on table
2. Go to all tasks, and Generate SQL Script
3. Click on the Preview button.
4. The SQL DDL generated has the example I mentioned about checking for existing tables.

For the previous example I posted I changed it to look for temporary tables in the real sense that they were temporary. For tables that exist in the system catalog you could use the following code to check for them and create / drop them:

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

CREATE TABLE [dbo].[FOO] (
[A] [char] (10) NULL,
[B] [char] (10) NULL ,
[C] [datetime] NULL)
GO

Sorry if I mis-directed on the prevous post. Also, if you are using these tables just for processing I would encourage you to investigate table variables (if you are using SQL 2000). They are variables in the true sense of a variable and as such reside in memory and make processing LOTS faster. As a disclaimer, let me say that it depends on what is being done too but they can speed things up. So the above table would be declared as a table variable as such:

declare @proc_table table (a char(10), b char(10), c datetime)

You can insert/update/delete and join with the table variable just like you can with a real (or temporary) table.

Hope this helps.

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-05 : 15:59:46

quote:
OBJECTPROPERTY(id, N'IsUserTable') = 1

Is this always equal to 1? Do I need to make allowances for this to change? Why is this a better way than the script I posted earlier?

quote:
Sorry if I mis-directed on the prevous post.

Please don't worry about it. I started it when I said 'temp' tables. :O)

We're using SQL7 here. I have the rest of the proc written and it runs without issue. Until this morning... when it tried to create the tables and they were already there because you-know-who was analyzing data yesterday and forgot to delete them when she was finished!

Thanks again for your help!
Teresa

"Happiness is found along the way;
not at the end of the road."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources