0

What is the best way to check if more than one table exist in a database using SQL Server, I tried with this:

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'Providers') OR NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'Customers') 

but is there a better way to do it ?

0

Try this...

IF 1 < (
        SELECT count(*)
        FROM (
            SELECT TOP 2 TABLE_NAME
            FROM INFORMATION_SCHEMA.tables
            WHERE TABLE_TYPE = 'BASE TABLE'
            ) a
        )
    PRINT 'More than one table exists'
0

Well if you turn on certain options in SSMS like "Check for object existence" you will get a script that looks like this when dropping and re-creating an object (e.g. table):

IF NOT EXISTS 
    (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'[<schema>].[<object_name>]') 
        AND type in (N'U')
    )

Is it better? Uncertain.
Is it different? Yes.
Is it what Microsoft would do? Yes.

0

There is so many ways you can do it. But as simple way you can do it Using INFORMATION_SCHEMA.TABLES view.

We can write a query like below to check if a Customers Table exists in the current database. As below TSQL code

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'Customers')
BEGIN
  PRINT 'Table Exists'
END

Or

IF EXISTS (SELECT * 
    FROM SqlHintsDemoDB.INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_SCHEMA = N'dbo'  AND TABLE_NAME = N'Customers')
BEGIN
  PRINT 'Table Exists'
END

For ref How to check if a Table exists in Sql Server

----After Your Comments Edit----------------

If You want to check in more than one table exist in a database using SQL Server. Then the follow mention my TSQL query.

SELECT  DISTINCT DB_NAME(database_id) 
FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL)
WHERE OBJECT_NAME(object_id,database_id) IN ('Tablename1','Tablename2');

It is Tested Ok in Test Environment.

I hope so that it will help out to you.

Note:- Before using in Production environment better will be that to test in Test Environment.

  • I want to check if more than one table exists not only one – rainman Jan 10 '17 at 10:54
  • @rainman,Go through my 'After your comments edit' code. I mean to say that second code . – Md Haidar Ali Khan Jan 10 '17 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.