Why OBJECT_ID used while checking if a table exists or not

Question

I need to check if a table in SQL exist or not.

If not it must create one automatically.

Now I researched and found this code:

IF  NOT EXISTS (SELECT * FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))  BEGIN CREATE TABLE [dbo].[YourTable]( .... .... .... )   END 

Can anyone explain why it says where object_id = OBJECT_ID and what should I put in its place?

Answer 1

I like this syntax:

if(object_id(N'[dbo].[YourTable]', 'U') is not null) ... 

Where object_id takes the 2 char type of object as the second parameter. You can find the list of Object types listed below in the sys.objects documentation:

• AF = Aggregate function (CLR)
• C = CHECK constraint
• D = DEFAULT (constraint or stand-alone)
• F = FOREIGN KEY constraint
• FN = SQL scalar function
• FS = Assembly (CLR) scalar-function
• FT = Assembly (CLR) table-valued function
• IF = SQL inline table-valued function
• IT = Internal table
• P = SQL Stored Procedure
• PC = Assembly (CLR) stored-procedure
• PG = Plan guide
• PK = PRIMARY KEY constraint
• R = Rule (old-style, stand-alone)
• RF = Replication-filter-procedure
• S = System base table
• SN = Synonym
• SO = Sequence object
• SQ = Service queue
• TA = Assembly (CLR) DML trigger
• TF = SQL table-valued-function
• TR = SQL DML trigger
• TT = Table type
• U = Table (user-defined)
• UQ = UNIQUE constraint
• V = View
• X = Extended stored procedure

Answer 2

The ISO SQL way to check existence of a table level object is the INFORMATION_SCHEMA.TABLES view

There's nothing wrong with looking at sys.objects but.... INFORMATION_SCHEMA.TABLES is a bit more declarative -- and it's cross platform (which often doesn't matter at all but meh still nice.)

I think this is probably more readable for a new coder though:

DECLARE @tableName SYSNAME = 'tbfoo' DECLARE @schemaNAme SYSNAME = 'fooSchema'  IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName ) BEGIN     RAISERROR('%s exists in schema: %s', 0, 1, @tableName, @schemaName) END ELSE BEGIN     RAISERROR('%s DOES NOT EXIST in schema: %s', 0, 1, @tableName, @schemaName) END 

Don't worry about the RAISERROR command -- its just a nice way of printing formatted messages.

You can query the INFORMATION_SCHEMA view to get a sense of what's in it.

SELECT TOP 5 * FROM INFORMATION_SCHEMA.TABLES 

As you can see -- you can reference schemas and catalogs by name rather than looking up their ID with OBJECT_ID()