Checking if a table exists dynamically

Question

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.

Possible duplicate of [Check if table exists in SQL Server](https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server) — Andrey Korneyev, Jun 28 '17 at 11:17
Almost any trivial question has already been asked and been answered at StackOverflow in the past. Just do not hesitate to use Google. — Andrey Korneyev, Jun 28 '17 at 11:19
More than one way but the most concise is `IF OBJECT_ID(N'YourDdatabase.YourSchema.YourTable', 'U') IS NOT NULL` — Dan Guzman, Jun 28 '17 at 11:19
https://docs.google.com/document/d/12a74Wf-NEZ5iI1Hz2TtJ7T_2wKqDKxXZCJ6zZAETMAg/edit?usp=sharing — A.Antony, Jun 28 '17 at 11:27
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... — Tyron78, Jun 28 '17 at 11:46
I added it as answer - it would be really kind of you to accept it. :-) Thanks. — Tyron78, Jun 29 '17 at 06:44

score 2 · Answer 1 · answered Jun 28 '17 at 13:21

2

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'

answered Jun 28 '17 at 13:21

Ben Thul

31,080
4
45
68

score 0 · Answer 2 · edited Jun 28 '17 at 14:09

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

You should not use the column TABLE_SCHEMA as reliable source to determine the schema. https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/tables-transact-sql — Sean Lange, Jun 28 '17 at 13:21
Nothing wrong with writing a query, especially if you want more information. But you need to query sys.objects and sys.schemas. :) — Sean Lange, Jun 28 '17 at 13:24
@SeanLange : updated my answer with alternate approach. thanks — Prabhat G, Jun 28 '17 at 13:47
You could also join sys.objects to sys.schemas so you can do it in a single query. But this should work anyway. — Sean Lange, Jun 28 '17 at 13:52

score 0 · Accepted Answer · answered Jun 29 '17 at 06:44

0

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...

answered Jun 29 '17 at 06:44

Tyron78

4,117
2
17
32

Checking if a table exists dynamically

3 Answers3