?
Solved

Check if Table Exists from List

Posted on 2014-03-17
4
Medium Priority
?
261 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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you 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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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