Solved

Check if Table Exists from List

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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 …
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now