Solved

Check if Table Exists from List

Posted on 2014-03-17
4
256 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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