Drop table TestTable
...you should really have something more robust, that returns informative messagess and checks for errors. Something like this:
Declare @Error int
if exists (select * from sys.objects where name = 'TestTable' and type = 'u' and schema_id = schema_id('dbo') ) begin
-- The table exists, prepare to delete it
Drop table dbo.TestTable
Select @Error = @@Error
if @Error <> 0 begin
RAISERROR ('Error dropping table dbo.TestTable' ,16 ,1)
end
print 'Successfully dropped Table TestTable.'
end
else begin
print 'Table TestTable does not exist or has already been deleted.'
end
But do you want to constantly be rewriting that piece of code as you need to drop different objects? Absolutely not! That's why I wrote the stored procedure above called sp_DropDatabaseObject that will delete many different types of database objects (tables, procedures, views, functions, and indexes). It incorporates all the functionality above (error trapping, good error messages), in a reusable procedure. It's created in the master database, so that it can be called from any database. Note that at the end, I call sp_MS_marksystemobject to mark it as a system object - this allows it to have the context of the calling database even though it's located in the master database.
Here are some examples of how to run sp_DropDatabaseObject
-- Drop table
exec sp_DropDatabaseObject 'dbo', 'TestTable', 'u'
-- Drop procedure
exec sp_DropDatabaseObject 'dbo', 'TestProcedure', 'p'
-- Drop index
exec sp_DropDatabaseObject 'dbo', 'TestTable.index1', 'i'
-- Drop View
exec sp_DropDatabaseObject 'dbo', 'TestView', 'v'
-- Drop function
exec sp_DropDatabaseObject 'dbo', 'TestFunction', 'fn'
And below is the code for the stored procedure. I haven't yet modified it to use SQL 2005 error trapping (try/catch), but that would be a definite improvement.
use master
go
Create procedure dbo.sp_DropDatabaseObject
@pSchemaName varchar(100) -- the schema the object belongs to, when applicable
,@pObjectName sysname -- name of the object to drop, including schema (i.e. dbo.TableName)
,@pObjectType char(2) -- type of object to be dropped.
-- Can be 'U', 'V', 'P', 'FN', 'I' (for table, view, procedure, function, and index)
as
----------------------------------------------------------------------------
-- Declarations
----------------------------------------------------------------------------
declare -- Standard declares
@FALSE tinyint -- Boolean false.
,@TRUE tinyint -- Boolean true.
,@ExitCode int -- Return value of this procedure.
,@rc int -- Return code from a called SP.
,@Error int -- Store error codes returned by statements and procedures (@@error).
,@RaiseMessage varchar(1000) -- Creates helpful message to be raised when running.
declare -- sp specific declares
@SingleQuote nchar(1)
,@SQL nvarchar(4000)
,@IndexTableName varchar(50)
,@IndexIndexName varchar(50)
----------------------------------------------------------------------------
-- Initializations
----------------------------------------------------------------------------
select -- Standard constants
@FALSE = 0
,@TRUE = 1
,@ExitCode = 0
,@rc = 0
,@Error = 0
Select
@SingleQuote = char(39)
----------------------------------------------------------------------------
-- Validate that all objects have an appropriate ObjectType
----------------------------------------------------------------------------
if @pObjectType not in ('U', 'V', 'P', 'FN', 'I') begin
select @RaiseMessage = 'Invalid ObjectType value: ' + @pObjectType
goto ErrorHandler
end
----------------------------------------------------------------------------
-- Put together the SQL to drop the database object
----------------------------------------------------------------------------
if @pObjectType = 'U' begin
if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
-- The table exists, prepare to delete it
Select @SQL = 'Drop table ' + @pSchemaName + '.' + @pObjectName
end
else begin
select @RaiseMessage = 'Table ' + @pObjectName + ' does not exist or has already been deleted'
print @RaiseMessage
goto ExitProc
end
end
if @pObjectType = 'V' begin
if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
-- The view exists, prepare to delete it
Select @SQL = 'Drop view ' + @pSchemaName + '.' + @pObjectName
end
else begin
select @RaiseMessage = 'View ' + @pObjectName + ' does not exist or has already been deleted'
print @RaiseMessage
goto ExitProc
end
end
if @pObjectType = 'P' begin
if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
-- The procedure exists, prepare to delete it
Select @SQL = 'Drop procedure ' + @pSchemaName + '.' + @pObjectName
end
else begin
select @RaiseMessage = 'Procedure ' + @pObjectName + ' does not exist or has already been deleted'
print @RaiseMessage
goto ExitProc
end
end
if @pObjectType = 'FN' begin
if exists (select * from sys.objects where name = @pObjectName and type = @pObjectType and schema_id = schema_id(@pSchemaName) ) begin
-- The function exists, prepare to delete it
Select @SQL = 'Drop function ' + @pSchemaName + '.' + @pObjectName
end
else begin
select @RaiseMessage = 'Function ' + @pObjectName + ' does not exist or has already been deleted'
print @RaiseMessage
goto ExitProc
end
end
if @pObjectType = 'I' begin
-- Parse out the table/index names to be able to test for index existance easily
Select @IndexTableName = substring(@pObjectName, 1, CHARINDEX('.', @pObjectName) - 1)
Select @IndexIndexName = substring(@pObjectName, CHARINDEX('.', @pObjectName) + 1, 50 )
If IndexProperty(OBJECT_ID(@IndexTableName),@IndexIndexName,'IndexID') IS not NULL begin
-- Check first whether it's a primary key
if exists
(
select * from sys.indexes where is_primary_key = @TRUE and object_name(object_id) = @IndexTableName and name = @IndexIndexName
)
begin
Select @SQL = 'Alter table ' + @pSchemaName + '.' + @IndexTableName + ' drop constraint ' + @IndexIndexName
end
else begin
Select @SQL = 'Drop Index ' + @pSchemaName + '.' + @pObjectName
end
end
else begin
select @RaiseMessage = 'Index ' + @pObjectName + ' does not exist or has already been deleted'
print @RaiseMessage
goto ExitProc
end
end
----------------------------------------------------------------------------
-- Drop the database object
----------------------------------------------------------------------------
if @SQL is not null begin
Exec @RC = sp_executesql @sql
select @Error = @@Error
if @Error <> 0 or @RC <> 0 begin
select @RaiseMessage = 'Error dropping object : ' + @pObjectName + ' using sql statement: ' + @SQL
goto ErrorHandler
end
Select @RaiseMessage = 'Completed dropping object: ' + @pObjectName + ' using sql statement: ' + @SQL
print @RaiseMessage
end
goto ExitProc
----------------------------------------------------------------------------
-- Error Handler
----------------------------------------------------------------------------
ErrorHandler:
select @ExitCode = -100
-- Print the Error Message now that will kill isql.
RAISERROR (
@RaiseMessage
,16 -- Severity.
,1 -- State.
)
goto ExitProc
----------------------------------------------------------------------------
-- Exit Procedure
----------------------------------------------------------------------------
ExitProc:
return (@ExitCode)
go
-- Marks it as a system object. Otherwise, it may return object information from the master database instead of the calling database
EXEC sys.sp_MS_marksystemobject sp_DropDatabaseObject
GO
No comments:
Post a Comment