Dynamic SQL to drop a table - 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 2000 Forums

Transact-SQL (2000)

Dynamic SQL to drop a table

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-06-20 : 03:48:48

How can I convert the below shown query to dynamic since the table name keeps on changing :

Next time it will be tbl_201 or tbl_119

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbl_116]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[tbl_116]
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-20 : 04:12:46

Why are you having denormalised structure?

declare @table varchar(8000)
Select @table='[tbl_116]'
EXEC(
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'''@table+''') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
DROP TABLE [dbo].'+@table)
END')

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-20 : 04:19:12

Also, Make sure you read this fully
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources