Solved

Check if Table Exists from List

Posted on 2014-03-17
4
258 Views
Last Modified: 2014-03-19
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
0
Comment
Question by:mike1142
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39933624
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
 

Author Comment

by:mike1142
ID: 39934263
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
 

Author Comment

by:mike1142
ID: 39941316
;
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question