Check to see if table exists and if table is null

Question

I'm building a package in SSIS that first acquires source data from from another server and loads into my server. From there I have another package that manipulates this source data and loads a new table.

I want to prevent this 'manipulation' package from running if something failed on my 'source' package. I can talk it out but not Syntax it out...

If MyTABLE exists and count(*) from MyTABLE > 0 then 'GOOD' else 'BAD' I was going to have a constraint that wouldn't run the rest of the package if anything came back bad. If this is an asinine way of achieving this then please show me the easier way to achieve this.

The ultimate goal would be to have a list of 'GOOD' or 'BAD' and the table name as the next column so if anything fails I can get an email of my query that would quickly show me what is bad and what is good.

Source_Check          Source_Table
  GOOD                   TABLE1
  BAD                    TABLE2
  GOOD                   TABLE3

etc.....

my issue is I get an error trying to count(*) from a table that doesn't exist... Not sure how to overcome this :(

There are probably easier ways to go about this, I'm just a newb is all. Could phrase my search right to get any results from google.

Try this: http://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server — jacobappleton, Apr 21 '15 at 18:12
You could put some code in the OnError handler that handles the result the same as if it had returned 0 count. — Tab Alleman, Apr 21 '15 at 18:13
@jacobappleton: thank you as I was making this far too hard. Technically if records are < 0 or table doesn't exist then it needs to fail so just checting that object_id is not null achieves both. — user3486773, Apr 21 '15 at 18:29
@user3486773 No worries, we use that syntax a lot when writing pre/post deployment scripts for SSDT database projects. — jacobappleton, Apr 21 '15 at 19:31

score 2 · Answer 1 · answered Apr 21 '15 at 18:17

Metadata is going to be your friend. Assuming SQL Server, you can write a simple query like

SELECT
    *
FROM
    sys.schemas AS S
    INNER JOIN sys.tables AS T
    ON T.schema_id = S.schema_id
WHERE
    S.name = N'dbo'    
    AND T.name = N'SomeTable';

If that returns a result, then you know your table exists.

The problem with SELECT COUNT(*) is that the table must exist. It also locks forces you to crawl the table. There are tricks for addressing this but my favorite is to go back to the metadata well. Look at this query. It's going to generate a list of all the tables and their row counts in no time at all.

SELECT
    s.[Name] as [Schema]
,   t.[name] as [Table]
,   SUM(p.rows) as [RowCount]
FROM
    sys.schemas s
    LEFT OUTER JOIN 
        sys.tables t
        ON s.schema_id = t.schema_id
    LEFT OUTER JOIN 
        sys.partitions p
        ON t.object_id = p.object_id
    LEFT OUTER JOIN  
        sys.allocation_units a
        ON p.partition_id = a.container_id
WHERE
    p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
    AND p.rows is not null
    AND a.type = 1  -- row-data only , not LOB
GROUP BY 
    s.[Name]
,   t.[name]
ORDER BY 
    1
,   2

Now you can compare that resultset to your list of tables that may or may not exist.

score 0 · Answer 2 · answered Apr 21 '15 at 18:40

You could try something like this:

DECLARE @SourceTables TABLE (tbl_name VARCHAR(100))
INSERT INTO @SourceTables VALUES('TABLE1'),('TABLE2'),('TABLE3');

WITH CTE
AS
(
    SELECT  o.name AS tbl_name,
            i.[Rows] AS row_count
    FROM sysindexes i
    INNER JOIN sysobjects o
        ON i.id = o.id
    WHERE  i.IndId < 2
            AND xtype = 'U'
)

--Returns tables that don't exist

SELECT  CASE
            WHEN B.tbl_name IS NULL AND B.row_count > 0 --when there is no table and row count is greater than 0, then good
                THEN 'Good'
            ELSE 'Bad'
        END AS Source_Check,
        A.tbl_name
FROM @SourceTables A
LEFT JOIN CTE B
    ON A.tbl_name = B.tbl_name

Theoretical Results:

Source_Check tbl_name
------------ -------------------------
Good         TABLE1
Good         TABLE2
Bad          TABLE3

score 0 · Answer 3 · answered Apr 21 '15 at 18:48

I assume you're using SQL Server based on the SSIS package. You could simplify things using OBJECT_ID Try Something Like the following....

CREATE TABLE #OUTPUT(Source_Check VARCHAR(10), Source_Table VARCHAR(10))
IF OBJECT_ID('DBNAME.dbo.Table1') IS NOT NULL 
BEGIN
   INSERT INTO #OUTPUT VALUES ('GOOD', 'TABLE1')
END
ELSE 
   INSERT INTO #OUTPUT VALUES ('BAD', 'TABLE1')
END
IF OBJECT_ID('DBNAME.dbo.Table2') IS NOT NULL 
BEGIN
   INSERT INTO #OUTPUT VALUES ('GOOD', 'TABLE2')
END
ELSE 
   INSERT INTO #OUTPUT VALUES ('BAD', 'TABLE2')
END
IF OBJECT_ID('DBNAME.dbo.Table3') IS NOT NULL 
BEGIN
   INSERT INTO #OUTPUT VALUES ('GOOD', 'TABLE3')
END
ELSE 
   INSERT INTO #OUTPUT VALUES ('BAD', 'TABLE3')
END
SELECT * FROM #OUTPUT

Check to see if table exists and if table is null

3 Answers3