drop table with variable name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

drop table with variable name

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-05-08 : 06:32:14

Hi
If I wanted to check if a tables existed before dropping it so it doesn't kick up a table does not exist error in the program I would use the code below. However my problem is that the schema name is not [dbo] but has a variable name created by the user at runtime. I have been trying to change the code below to dynamic sql so I can add the variable $schemaname instead of [dbo] below but I can't get it to work.

Any help would be appreciated

thanks



USE [database]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[table]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[table]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-08 : 06:43:55

you could do something like this:


USE [Tempdb]
GO

CREATE SCHEMA foo
GO

CREATE TABLE foo.bar ([woo] BIT)
INSERT foo.bar VALUES (1)
GO

IF OBJECT_ID('foo.bar') IS NOT NULL
	SELECT 'Foo.Bar Exists'
ELSE
	SELECT 'Foo.Bar DOES NOT Exist'
GO

DECLARE @schemaName SYSNAME = 'foo'
DECLARE @tableName SYSNAME = 'bar'

DECLARE @QualifiedName SYSNAME = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
DECLARE @sql NVARCHAR(MAX)

IF OBJECT_ID(@QualifiedName) IS NOT NULL
BEGIN
	SET @sql = 'DROP TABLE ' + @QualifiedName
	PRINT @sql
	EXEC sp_executeSql @sql
END


IF OBJECT_ID('foo.bar') IS NOT NULL
	SELECT 'Foo.Bar Exists'
ELSE
	SELECT 'Foo.Bar DOES NOT Exist'
GO

-- Cleanup
DROP SCHEMA foo
GO

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-08 : 06:45:04

Note I wrapped all input using QUOTENAME()

This should give good protection from sql injection. However it won't stop anyone from dropping the wrong table if they get the parameter names wrong.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

spinningtop
Starting Member

29 Posts

Posted - 2012-05-08 : 11:49:07

Thanks very much.
This works very well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources