August 23, 2012 at 5:44 am
HI,
For Droping a table if exists I am using below commands ,Is there any other efficient way of doing same
if exists (select * from dbo.sysobjects where name ='ABC_school')
DROP TABLE XYZ_DB..ABC_school
August 23, 2012 at 5:50 am
I tend to simply check for the object_id a lot:
I also tend to put the command for create/drop/alter for the table in dynamic SQL to avoid syntax checking.
IF OBJECT_ID('ABC_School') IS NOT NULL
EXEC('DROP TABLE ABC_School')
Lowell
August 23, 2012 at 5:52 am
Why is avoiding syntax checking a good idea?
August 23, 2012 at 6:07 am
laurie-789651 (8/23/2012)
Why is avoiding syntax checking a good idea?
It's a holdover from how my scripts get deployed; trying to avoid GO statements, so everything gets wrapped into dynamic SQL for the DDL commands; I'd have to create the scenario as a demo, but as i remember it, for example if we add a new column to an existing table, and then UPDATE that table with a command that references the new column, you get a syntax error because the column didn't exist when the entire command was validated.
CREATE TABLE Bananas(BananaId int identity(1,1) not null primary key)
GO
ALTER TABLE Bananas ADD NewCol varchar(30)
UPDATE Bananas
SET NewCol = 'Green Panamanian'
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NewCol'.
Lowell
August 23, 2012 at 6:08 am
I would suggest:
IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[ABC_SCHEMA].[ABC_TABLE]') AND [Type] IN (N'U'))
DROP TABLE [ABC_SCHEMA].[ABC_TABLE]
Because:
1. It's safer to check the schema in witch the table exists, to avoid deletion of similar table name in different schema or script execution failure.
2. Checking de object type, avoid script execution failure in case there is a view with similar name.
August 23, 2012 at 6:22 am
Lowell (8/23/2012)
laurie-789651 (8/23/2012)
Why is avoiding syntax checking a good idea?It's a holdover from how my scripts get deployed; trying to avoid GO statements, so everything gets wrapped into dynamic SQL for the DDL commands; I'd have to create the scenario as a demo, but as i remember it, for example if we add a new column to an existing table, and then UPDATE that table with a command that references the new column, you get a syntax error because the column didn't exist when the entire command was validated.
CREATE TABLE Bananas(BananaId int identity(1,1) not null primary key)
GO
ALTER TABLE Bananas ADD NewCol varchar(30)
UPDATE Bananas
SET NewCol = 'Green Panamanian'
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NewCol'.
Yes - I've come across that myself.
I was interested to know because at this site there are huge sections of dynamic SQL with no dynamic element. I'll check & see if that offers an explanation.
Thanks
August 23, 2012 at 6:39 am
only way
August 23, 2012 at 6:39 am
Thanks a lot for suggestions: 🙂
August 23, 2012 at 6:45 am
MSzI (8/23/2012)
I would suggest:
IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[ABC_SCHEMA].[ABC_TABLE]') AND [Type] IN (N'U'))
DROP TABLE [ABC_SCHEMA].[ABC_TABLE]
Because:
1. It's safer to check the schema in witch the table exists, to avoid deletion of similar table name in different schema or script execution failure.
2. Checking de object type, avoid script execution failure in case there is a view with similar name.
I agree with your points, but there's no need for the verbose syntax. Something like this does the same in less space:
if object_id('[schema].[Test]', 'U') is null
--do something
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 23, 2012 at 7:13 am
Phil Parkin (8/23/2012)
MSzI (8/23/2012)
I would suggest:
IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[ABC_SCHEMA].[ABC_TABLE]') AND [Type] IN (N'U'))
DROP TABLE [ABC_SCHEMA].[ABC_TABLE]
Because:
1. It's safer to check the schema in witch the table exists, to avoid deletion of similar table name in different schema or script execution failure.
2. Checking de object type, avoid script execution failure in case there is a view with similar name.
I agree with your points, but there's no need for the verbose syntax. Something like this does the same in less space:
if object_id('[schema].[Test]', 'U') is null--do something
I agree on using the object_id, but for a drop table you would want to check that the object_id is NOT null:
if object_id('[schema].[Test]', 'U') is not null
drop table [schema].[Test]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply