0

I have a list of table names and I want to check whether each one of them exists in database or not. I know how to check if a table exists, and I can union select expressions for each table name, but there must be an easier solution. This is my current code:

select 'TableName1', CASE
         WHEN EXISTS(SELECT TABLE_NAME 
                     FROM   INFORMATION_SCHEMA.TABLES
                     WHERE  TABLE_NAME = 'TableName1') THEN 1
         ELSE 0
         end
union
select 'TableName2', CASE
         WHEN EXISTS(SELECT TABLE_NAME 
                     FROM   INFORMATION_SCHEMA.TABLES
                     WHERE  TABLE_NAME = 'TableName2') THEN 1
         ELSE 0
         end
-- and so on
saastn
  • 5,717
  • 8
  • 47
  • 78
  • try this SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' order by TABLE_NAME it give list of table in tha database – Prasanna Kumar J Aug 12 '17 at 06:54
  • @PrasannaKumarJ I don't want list of all tables, I want to know if a list of tables (e.g. `'TN1'`, `'TN2'`, `'TN3'`, ...) exists in DB, I want a flag for each one of them indicating if table exists or not. – saastn Aug 12 '17 at 07:01

4 Answers4

4

You can use this

SELECT 
     table_name,
     CASE WHEN OBJECT_ID(table_name,'U') IS NULL THEN 0 ELSE 1 END
FROM (VALUES
      ('dbo.table1'),
      ('dbo.table2')) V(table_name)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

To get the EXACT output you specified in your question try this. P.Salmon's answer is much more elegant, but this works for me:-

USE MASTER;

DECLARE @tables TABLE(RowNum int Identity, TableName varchar(255), DoesExist bit);
INSERT INTO @tables
SELECT 'Table1',null
UNION
SELECT 'spt_fallback_dev',null

DECLARE @TableName varchar(255), @RowPosition int=0, @TotalRows int = (SELECT COUNT(*) FROM @tables)
WHILE (@RowPosition <= @TotalRows)
BEGIN
    SET @RowPosition = @RowPosition + 1;
    SELECT @TableName = (SELECT TableName FROM @tables WHERE RowNum = @RowPosition);

    UPDATE @tables 
    SET DoesExist = (SELECT CASE WHEN EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME=@TableName) THEN 1 ELSE 0 END )
    WHERE RowNum = @RowPosition;

END

SELECT * FROM @tables;
Mr Slim
  • 1,458
  • 3
  • 17
  • 28
0

You could use intersect and except. For example

DROP TABLE #T
CREATE TABLE #T (TABLE_NAME VARCHAR(100))
INSERT INTO #T VALUES
('MYTABLE'),('TABLE2'),('ZZZ')

print 'IN INFORMATION_SCHEMA'
SELECT TABLE_NAME FROM #T   
INTERSECT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
print 'NOT IN ONFORMATION SCHEMA'
SELECT TABLE_NAME FROM #T   
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

(3 row(s) affected)
IN INFORMATION_SCHEMA
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
MYTABLE
TABLE2

(2 row(s) affected)

NOT IN ONFORMATION SCHEMA
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
ZZZ

(1 row(s) affected)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Thanks, but I can't run it: `Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the INTERSECT operation.` – saastn Aug 12 '17 at 07:06
  • Yes, and error message shows that my database uses Arabic collation. – saastn Aug 12 '17 at 07:15
0
CREATE TABLE #yourtable (TABLE_NAME VARCHAR(100)COLLATE SQL_Latin1_General_CP1_CS_AS NULL)
    INSERT INTO #yourtable  VALUES
    ('TableName1'),('TableName2'),soon...

    SELECT t.table_name,(case when d.table_name is null then 0 else 1 end) flag FROM INFORMATION_SCHEMA.TABLES t
    left join #yourtable d on d.table_name COLLATE SQL_Latin1_General_CP1_CI_AS =t.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
Prasanna Kumar J
  • 1,288
  • 3
  • 17
  • 35