To delete a permanent table (not a temp table) in a sql server stored procedure, use this code: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TableName]') AND type in (N'U')) DROP TABLE [TableName] However, we have run into situations where the drop table doesn't work, and you get an error message. It turned out to be a security permissions issue. Yet all other commands in the stored procedure worked. But the drop table command does not. The workaround was to run the drop table command under a different user security account: EXECUTE AS User='DOMAIN\UserID'; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Tablename]') AND type in (N'U')) DROP TABLE [TableName] REVERT; |
SQL Server >