T-SQL

• sunandas

  Old Hand

  Points: 357

  More actions

  April 22, 2008 at 5:32 am

  #70347

  HI,

  Can you please tell me whether we can drop more than one objects in a single drop command without using any procedure.

  For example

  drop table (select name from sysobjects where name like 'e%' and xtype='U')

  error is coming

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near '('.

  Please help me out.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 22, 2008 at 5:46 am

  #805419

  I don't think so. But you can use a single SELECT statement to generate the SQL that you can use to delete the tables.

  John

• msaxe

  SSC Enthusiast

  Points: 148

  More actions

  April 23, 2008 at 9:03 am

  #806122

  ...a quick example for you...

  -- create some dummy tables for testing...

  create table #tbl1 (id int)

  create table #tbl2 (id int)

  create table #tbl3 (id int)

  create table #tbl4 (table_name varchar(20))

  insert into #tbl4 select '#tbl1' union select '#tbl2' union select '#tbl3'

  select * from #tbl4

  -- create the drop commands for the tables we want to drop.

  declare @sql nvarchar(4000)

  select @sql=''

  select @sql=@sql + ' drop table '+table_name from #tbl4

  select @sql

  -- run the sql

  execute sp_executesql @sql

  drop table #tbl4

  --so...for you're purposes, something like this...

  declare @sql nvarchar(4000) select @sql=''

  select @sql=@sql + ' drop table '+[name] from sysobjects where [name] like 'e%' and xtype='U'

  execute sp_executesql @sql

• sunandas

  Old Hand

  Points: 357

  More actions

  April 24, 2008 at 12:46 am

  #806385

  Thanks a lot.