April 2, 2008 at 2:26 am
Can anyone tell me how I can drop a table if it exists.
this doesn't work...........
ALTER PROCEDURE [dbo].[spClearTempCoordtables]
AS
BEGIN
if exists (select * from dbo.coordinates)
drop table dbo.coordinates
END
gives..........
Msg 208, Level 16, State 1, Procedure spClearTempCoordtables, Line 11
Invalid object name 'dbo.coordinates'.
(1 row(s) affected)
thanks
April 2, 2008 at 2:49 am
you can use ...
if object_id('dbo.yourtable') is not null
begin
drop table dbo.yourtable
end
Keep in mind this is not a best practice because you need elevated auth to be able to drop or create a table.
- maybe # temp tables may help out (I don't know the procs functional needs)
- To avoid everyone executing the proc needs to be dbo-member, you could use the with execute as setting.
(check BOL)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2008 at 3:47 am
Use below replace GOLD_BROKER_OUTPUT WITH THE TABLE YOU NEED TO DROP IS EXISTS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GOLD_BROKER_OUTPUT]') AND type in (N'U'))
DROP TABLE [dbo].[GOLD_BROKER_OUTPUT]
April 3, 2008 at 4:24 pm
If you have access to the database via SS Management Studio you can generate the T-SQL needed to drop the table by
right_clicking on the table and selecting:
Script Table as | DROP to | New Query Editor Window
or:
Script Table as | DROP to | File...
April 4, 2008 at 3:09 am
This is what I normaly use:
IF OBJECT_ID('tempdb..##tbltmp', 'U') IS NOT NULL
DROP TABLE ##tbltmp
November 24, 2008 at 5:00 pm
When you select a "DROP TO" from SQL SSMS, this is the SQL that generates...
USE [database]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].
') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].
January 27, 2010 at 7:02 am
Thanks for your posting. My scenario was slightly different and your solution didn't work for me as-is. My particular environment is with running SSMS 2008 and pointing to a SQL 2000 DB, so your examples didn't work due to the target DB being SQL Server 2000. Instead, I had to use the following:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<insert_tablename>]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[<insert_tablename>]
GO
Which is the SQL 2000 syntax as created by Enterprise Manager script generation.
Maybe someone else will find this helpful too.
January 27, 2010 at 7:12 am
Good feedback.
OT : Keep in mind you should avoid running in lower edition db-level ! So switch to your engines dblevel whenever you can !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 27, 2010 at 7:44 am
Oh yes. I should have mentioned that
it is being upgraded to 2008 like the rest
of the servers. DTS rewrites to SSIS need
to come first, IP, so until then . . .
: )
Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply