Check for Table Existance/Data At Same Time

• DB_Newbie2007

  SSCarpal Tunnel

  Points: 4438

  More actions

  February 1, 2017 at 8:48 am

  #326537

  I have a need to check various database(s) not only for existence of a table but also, if the table does exist, does it contain data.

  In the past I could have checked for existence, then wrapped a script in a variable and executed it, like this:
  DECLARE @SQL VARCHAR(MAX);
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'TempTest')
BEGIN
    SET @SQL = 'IF EXISTS (SELECT TOP 1 * FROM dbo.TempTest)
    ----==== Do Something
    SELECT ''Table/Data Exists'' as QryResult ELSE SELECT ''No Table/Data Found'' as ErrResult;'
    EXEC (@SQL);
END


  I just came up with this as an alternative. I am running the same query three times, testing when there is no table, there is a table and no data, there is a table and it does have data. 

  USE TEMPDB
GO

  ----==== Test for Existence of data
IF EXISTS (SELECT SUM(Row_Count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TempTest')
AND index_id < 2 ---- Check for
AND row_count > 0
GROUP BY OBJECT_NAME(object_id)
)
SELECT 'Table/Data Exists' AS QryResult ELSE SELECT 'No Table/Data Found' as ErrResult;
----==== Create table
CREATE TABLE dbo.TempTest (MyValue VARCHAR(20) );

  ----==== Test for Existence of data after table created
IF EXISTS (SELECT SUM(Row_Count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TempTest')
AND index_id < 2 ---- Check for
AND row_count > 0
GROUP BY OBJECT_NAME(object_id)
)
SELECT 'Table/Data Exists' AS QryResult ELSE SELECT 'No Table/Data Found' as ErrResult;

  ----==== Add in Data
INSERT INTO dbo.temptest (MyValue) SELECT 'ABC'

  ----==== Test for Existence of data (final query)
IF EXISTS (SELECT SUM(Row_Count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TempTest')
AND index_id < 2 ---- Check for
AND row_count > 0
GROUP BY OBJECT_NAME(object_id)
)
SELECT 'Table/Data Exists' AS QryResult ELSE SELECT 'No Table/Data Found' as ErrResult;

  IF OBJECT_ID(N'tempdb.dbo.temptest', N'U') IS NOT NULL DROP TABLE dbo.temptest;


  Any comments or suggestions as to a better method to test both for object existence and if the object contains data?

  Thanks! 🙂

  Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  February 1, 2017 at 9:12 am

  #1926126

  This does everything in one go; from there you can create and/or populate your table accordingly.

  DECLARE
     @RowCount bigint
,    @Schema sysname = 'TempTest'
,    @Table sysname = 'dbo';
SELECT @RowCount = SUM(row_count) FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = @Table
AND s.name = @Schema
AND o.type_desc = 'USER_TABLE';
SELECT
     @Schema AS SchemaName
  ,    @Table AS TableName
  ,    CASE
          WHEN @RowCount IS NULL THEN 'No'
          ELSE 'Yes'
       END AS TableExists
  ,    COALESCE(@RowCount,0) AS TableRowCount;