Technical Article

Drop a table and views

,

This is just a small script that will auto-check the system to see if the view or the table that you want to drop already exists in the system or not.

  1. To use this script pass the type as the first parameter. Type can either be a table or a view.
  2. The second arguement is the table or view name
  3. The third arguement is optional. If passed as 1 it will return the drop statement.
drop procedure [dbo].[sp_drop_object]
go
create procedure [dbo].[sp_drop_object]
   @i_type         varchar(20),
   @i_table_name   varchar(max),
   @i_debug        varchar(1) = 0
as
begin
   declare
   @v_sql    nvarchar(max)
   
   set @v_sql = 'if exists (select * from sysobjects where name =''' + @i_table_name + ''')'
              + ' drop '+ @i_type + ' ' + @i_table_name 
        
   if (@i_debug = 0) 
   exec sp_executesql @v_sql
   else
   select @v_sql

end

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (3)

You rated this post out of 5. Change rating