Check if Table Exists from List

I have a CSV file with 4 columns ID, TableName, Other1, Other2 (the last 2 don't matter)

I created a script to create a table and load the CSV into it using BULK INSERT

I now what to query each TableName in this new table against the tables that exists (or not) in another database.

This is close but not quite so maybe I am down the wrong road:

USE my_database
GO

CREATE TYPE SystemTableType AS TABLE
( SysTableName VARCHAR(40));
GO

CREATE PROCEDURE dbo.CheckTable
	@STT SystemTableType READONLY
	AS
	SET NOCOUNT ON
	SELECT * FROM my_other_database.sys.objects
	WHERE object_id = OBJECT_ID((SELECT 'dbo' + SysTableName FROM @STT))
	AND
	testmdb.sys.objects.type in (N'U')
	GO
	
DECLARE @SystemTableInp AS SystemTableType

INSERT INTO @SystemTableInp (SysTableName)
	SELECT TableName
	FROM dbo.TableWithTablesNamesIwantToCheck;

EXEC CheckTable @SystemTableInp;
GO

Open in new window


What is returned should be somethng like

TableName Exists
XYZ               (Y/N)

Thanks
mike1142Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
Something like this should suffice I think, it uses a LEFT JOIN and a case expression:
SELECT
      tCHECK.TableName
    , CASE WHEN sOBJ.Name IS NULL THEN 'N' ELSE 'Y' END AS "Table Exists?"
FROM [TableWithTablesNamesIwantToCheck] AS tCHECK
LEFT JOIN [sys].[objects] AS[sOBJ]
     ON tCHECK.TableName = sOBJ.Name

Open in new window

If you look at the following you will see this identifies the Y/N for items in the table "TableWithTablesNamesIwantToCheck" but it does not locate any tables that exist that are not listed in that table (in my sinple example the table [Locations] is ignored)
    CREATE TABLE UserProfiles
    	([UserId] int, [City] varchar(12))
    ;
    	
    INSERT INTO UserProfiles
    	([UserId], [City])
    VALUES
    	(1, 'New York'),
    	(2, 'Tokyo')
    ;
    
    CREATE TABLE Locations
    	([ID] int, [UserId] int, [City] varchar(10), [State] varchar(11))
    ;
    	
    INSERT INTO Locations
    	([ID], [UserId], [City], [State])
    VALUES
    	(1, 1, 'London', 'England'),
    	(2, 2, 'Paris', 'France')
    ;
    
    CREATE TABLE TableWithTablesNamesIwantToCheck
    	([TableName] varchar(20))
    ;
    	
    INSERT INTO TableWithTablesNamesIwantToCheck
    	([TableName])
    VALUES
    	('UserProfiles'),
        ('SomeOtherTableName')
    ;

**Query 1**:

    SELECT
          tCHECK.TableName
        , CASE WHEN sOBJ.Name IS NULL THEN 'N' ELSE 'Y' END AS "Table Exists?"
    FROM [TableWithTablesNamesIwantToCheck] AS tCHECK
    LEFT JOIN [sys].[objects] AS[sOBJ]
         ON tCHECK.TableName = sOBJ.Name
    	
    	
    

**[Results][2]**:
    
    |          TABLENAME | TABLE EXISTS? |
    |--------------------|---------------|
    |       UserProfiles |             Y |
    | SomeOtherTableName |             N |



  [1]: http://sqlfiddle.com/#!3/0777d/2

  [2]: http://sqlfiddle.com/#!3/0777d/2/0

Open in new window

0
 
mike1142Author Commented:
PortletPaul

I apologize for the heading, too many hours writing T-SQL.

Of course an obvious solution to join the tables. I will look at this in more detail but this probably is what I need.

Thank you
0
 
mike1142Author Commented:
;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.