Conditional sp_MSforeachtable not working

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  October 16, 2012 at 5:03 pm

  #263422

  I am trying to use the following script to move all tables from the s016PASP schema to dbo schema. So far it doesnt do anything and I can't see why. Can someone take a look and assist?

  exec sp_MSforeachtable '

  IF EXISTS(select * from information_schema.tables where TABLE_SCHEMA = ''s016PASP'' and Table_Name = ''?'')

  BEGIN

  ALTER SCHEMA dbo TRANSFER ?

  END'

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  October 16, 2012 at 5:20 pm

  #1549832

  I figured it out:

  exec sp_MSforeachtable '

  IF EXISTS(select * from information_schema.tables where TABLE_SCHEMA = ''s016PASP'' AND TABLE_NAME = parsename ("?", 1)

  )

  BEGIN

  ALTER SCHEMA dbo TRANSFER ?

  END'