Sunday, March 18, 2012

sp_DropTable: A simple T-SQL stored proc to drop a table in any database

A simple T-SQL procedure to drop a table in any database of the current server. The goal is to have the table removed, was it there or not. Feel free to use...


CREATE PROCEDURE [dbo].[sp_DropTable]
   @Database nvarchar(255),
   @Owner nvarchar(255),
   @Table nvarchar(255)
AS
BEGIN
   DECLARE @SQLStmt varchar(8000);
   SET NOCOUNT ON; SET @SQLStmt = '
   IF EXISTS (
      SELECT * FROM ' + @Database + '.sys.objects
      WHERE
         object_id = OBJECT_ID(N''' + @Database + '.' + @Owner + '.' + @Table + ''')
      AND type in (N''U'')
      )
   DROP TABLE ' + @Database + '.' + @Owner + '.' + @Table;
   EXEC (@SQLStmt);
END
GO


Eric

No comments:

Post a Comment