check if table exist in database and table is null data or not in SQL Server

In this article, I would like to show how to check if table exist in database and table is null data or not in MS SQL Server. Please examine example below:

I. Check if table exist in database

This example, I create a stored procedure to check whether table is exist in database or not. if the table  is exist in database​, I’ll delete the table. If the table is not exist in database, I'll create the table. If you didn't know how to create a stored procedure, please click here.

Create procedure [dbo].[sp_checktable]

as

  if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[tbl_tempTable]') and type in(N'U'))

    Begin     

       --delete table tbl_tempTable            

      drop table [dbo].[tbl_tempTable]

    end

  else

    begin

         --create table tbl_tempTable

      create table [dbo].[tbl_tempTable](userid int,name nvarchar(50),gender nvarchar(6))

    end  

     

II. Check if table is null data

 I create a procedure to check table. If the table has data, I'll delete all data in the table. I'll insert data into the table if the table is null data.

Create procedure [dbo].[sp_checkdata]

as

  --Check if table has data

     if exists (select * from tbl_tempInvoice)

    begin

         --delete data from table, tbl_tempTable

         delete tbl_tempInvoice

        end              

     else

    begin

         --insert data into table, tbl_tempTable

         insert into [dbo].[tbl_tempTable (userid,name,gender) values(1,'Tharoth','Female')

    end 

Hope this post is helpful for you.