Find and Drop SQL tables with a variable

• beachldy

  SSC Veteran

  Points: 228

  More actions

  August 28, 2002 at 4:50 pm

  #79928

  backend: SQL Frontend: Access /vba

  Sql tables are created using variables.

  These tables need to be dropped after a user ends their session. They were not created as # or # temp tables because they are used throughout various functions.

  All tables have a table name that starts with 'u and a user name, such as 'uMDS-table1',

  'uMDS-table2'.

  Select name from sysobjects where xtype = 'u' and name like '%mds%'

  returns the tables. How would I loop through and delete each table? Or is there another way to do this?

  Any code example would be appreciated.

  Thanks

• Antares686

  SSC Guru

  Points: 125444

  More actions

  August 28, 2002 at 5:16 pm

  #435583

  Be carefull but the way I do things like this where a specific condition can be met is like so.

  sp_MSForEachTable '

  if charindex(''mds'',''?'') != 0

  begin

  select ''?'' as verifyworksfirst

  --drop table ? --uncomment me once verified and want to run.

  end

  '

  This is just one possible way.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• dm

  Ten Centuries

  Points: 1105

  More actions

  August 28, 2002 at 5:25 pm

  #435585

  Here is one more way:

  declare TablesToDelete cursor

  read_only

  for

  Select name from sysobjects where xtype = 'u' and name like '%mds%'

  declare @name varchar(40)

  declare @sql varchar(100)-- for drop command

  open TablesToDelete

  fetch next from TablesToDelete into @name

  while (@@fetch_status = 0)

  begin

  set @sql = 'drop table ' + @name

  exec (@sql)

  fetch next from TablesToDelete into @name

  end

  close TablesToDelete

  deallocate TablesToDelete

  go

  Test all possible ways for the best one.

  Diane