CODEWITHSUNDEEP
sql-serverstored-procedures
A.Antony
A.Antony

Reputation: 15

Checking if a table exists dynamically

Can someone help me on how to check if a table exists dynamically when the db name,schema name,table name(the table name that has to be checked in that particular dbname and schemaname that was passed) are passed while executing a stored procedure.And so if the table exists then to perform a set of functions else to perform another set of functions.

Upvotes: 1

Views: 1994

Answers (3)

Tyron78
Tyron78

Reputation: 4197

You should concat DB name + '.' + Schema name + '.' + Table name, then execute the IF OBJECT_ID(@YourString) IS NOT NULL and put a BEGIN END afterwards... everything which has to be performed if the table exists goes between this BEGIN AND END...

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

For table schema, table name

Replace print statements with your queries you want to execute : 

CREATE PROCEDURE [dbo].[TableCheck]
    @tableschema VARCHAR(100),
    @tablename VARCHAR(100)
AS
BEGIN

    IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = @tableschema
                 AND  TABLE_NAME = @tablename))


        BEGIN
            print 'Table '+ @tablename +' Exists';
        END

    ELSE
        print 'Table '+ @tablename +' Does Not Exists';

END

output :

exec TableCheck dbo, test_table_name

Table test_table_name Does Not Exists

In similar manner, you can include database name with little Googling.

update based on comment : Another approach could be

CREATE PROCEDURE [dbo].[TableCheck]
    @tableschema VARCHAR(100),
    @tablename VARCHAR(100)
AS
BEGIN

    IF EXISTS ( SELECT  1 FROM sys.schemas WHERE   name = @tableschema ) 
    BEGIN
        IF (EXISTS (SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(@tablename) AND Type = N'U'))
            BEGIN
            print 'Table '+ @tablename +' Exists';
            END

        ELSE
            print 'Table '+ @tablename +' Does Not Exists';
    END

    ELSE
        print 'Schema '+@tableschema+' does not exists'
END

-----Sean Lange EDIT-------

Prabhat G asked to see how to join sys.objects and sys.schemas

select * 
from sys.objects o
join sys.schemas s on s.schema_id = o.schema_id
where o.name = @tablename
    and s.name = @tableschema

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

The OBJECT_ID() function will take a three-part name. So something like:

if (OBJECT_ID('db.schema.table') is not null)
   print 'table exists'
else
   print 'table doesn't exist'

Upvotes: 2

Related Questions