July 18, 2013 at 6:19 am
From the "Things You've Probably Wanted to Ask About SQL Server but Didn't Want to Sound Like a Noob" department.
Something that's always had me curious are all the different syntaxes used to determine if an object exists before doing something. I've run across 5 different ways of doing so and was wondering if the gurus hereabouts could chime in on the relative pros and cons of each method, if any.
Here are the ones I've encountered, looking for a table and dropping it as an example, numbered for discussion convenience:
#1
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo') BEGIN
DROP TABLE [dbo].[TableName]
END
#2
IF OBJECT_ID('dbo.TableName','U') IS NOT NULL
DROP TABLE dbo.TableName
GO
#3
if exists (select * from sys.objects where [object_id] = object_id('TableName'))
BEGIN
DROP TABLE dbo.TableName
END
#4
IF EXISTS(SELECT * FROM sysobjects WHERE Name='TableName' AND xtype='U')
DROP TABLE dbo.TableName
GO
--I believe this syntax is from SQL Server 2000, but I've seen similar for 2005 and up
#5
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
BEGIN
DROP TABLE dbo.TableName
END
I've used Method #2 pretty exclusively but got to wondering if there's any significant differences beyond just certain ones being more inclined to aggravate carpal tunnel from too much typing...
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 18, 2013 at 6:39 am
They all do the same thing, but use a different method to get there.
INFORMATION_SCHEMA are views that store metadata information about your database and if I'm not mistaken, they are vendor independent and defined by the ISO standard.
Information Schema Views (Transact-SQL)
OBJECT_ID(object) is a function that returns the object identification number of a schema-scoped object. If the object id cannot be found, the object does not exist.
sys.objects is a system view giving you information about the user-defined, schema-scoped objects in the database. In contrast to information_schema, this one is vendor-specific.
3 and 4 do exactly the same thing, only they use different methods to filter sys.objects.
5 and 1 are also the same, just a different way of writing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 18, 2013 at 6:48 am
To add to what Koen said, 4 uses sysobjects, which, from SQL Server 2005, has only been included for backward compatibility. Some of your statements test for the schema the object is in, some check that the object is actually a table. You can't really go wrong with 2, which, as well as being concise also checks the object type and schema.
John
July 19, 2013 at 5:23 am
Thanks for the input folks. Like I said, I've always used method 2 (OBJECT_ID), basically because that's what's shown throughout BOL, and though I've seen the other syntaxes in various places, I've never run across any real discussion of them.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply