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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy