drop table if exists
-
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
-
you can use ...
if object_id('dbo.yourtable') is not nullbegindrop table dbo.yourtableendKeep 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
-
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]
-
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...
-
This is what I normaly use:
IF OBJECT_ID('tempdb..##tbltmp', 'U') IS NOT NULL
DROP TABLE ##tbltmp
-
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].
-
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.
-
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
-
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