Is sp_MSforeachtable safe in this instance

• MRitch

  Ten Centuries

  Points: 1181

  More actions

  April 9, 2009 at 9:53 am

  #132681

  I want an opinion on whether this code is safe for what I want to do and what possible problems I should look out for.

  I have a production database that contains tables of product orders generated daily from my order entry system. We use the files for variable data printing. Currently, I manually delete the tables usually once a month. I try and keep the tables for 60 days for research and re-print purposes. I want to setup a SQL Agent job that will delete/drop the tables based on the create date. I have tested this code on a development database and it seems to works.

  ---

  Exec sp_MSforeachtable

  @command1 = "DROP TABLE ? PRINT '? dropped'",

  @whereand = "select * from sys.objects where type = 'U'

  AND IS_MS_SHIPPED = '0'

  AND DATEDIFF(DAY, CREATE_DATE, GETDATE()) > 60"

  ---

  Your thoughts or ideas are appreciated.

  For help on getting help, follow the link below:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 9, 2009 at 10:24 am

  #975205

  if any foreign keys exist between your tables,your sp_msforEachTable might fail...they have to be dropped in FK hierarchy order.

  there's a lot of ways to get your tables in FK order, here's a script incase you need it:

  SET NOCOUNT OFF

  DECLARE

  @Level INT,

  @MovedToNewLevel INT,

  @sql varchar(1024),

  @err varchar(125),

  @LastBatch int

  CREATE TABLE #Hierarchy

  (FKLevel INT,

  TblName VARCHAR(100),

  id Numeric

  )

  -- Populate the table

  INSERT INTO #Hierarchy

  select 0 AS 'FKLevel', name as 'TblName',id

  FROM sysobjects where xtype='U' and

  id not in (select rkeyid from sysforeignkeys) and

  id not in (select fkeyid from sysforeignkeys)

  INSERT INTO #Hierarchy select 1 AS 'FKLevel', name as 'TblName',id

  FROM sysobjects where xtype='U' and

  id not in (select id from #Hierarchy) and

  id in (select rkeyid from sysforeignkeys) and

  id not in (select fkeyid from sysforeignkeys)

  INSERT INTO #Hierarchy select 2 AS 'FKLevel', name as 'TblName',id

  FROM sysobjects where xtype='U' and

  id not in (select id from #Hierarchy) and

  id in (select fkeyid from sysforeignkeys)

  -- Set the variables

  set @Level=2

  set @MovedtoNewLevel=1

  WHILE @MovedtoNewLevel <> 0

  BEGIN

  set @LastBatch=@MovedtoNewLevel

  set @sql='update #Hierarchy set FKLevel = FKLevel + 1 where FKLevel=' + CAST(@Level as varchar) + ' and'

  set @sql=@sql + ' id in (select fkeyid from sysforeignkeys where fkeyid<>rkeyid and'

  set @sql=@sql + ' rkeyid in (select id from #Hierarchy where FKLevel=' + CAST(@Level as varchar) + ' ))'

  exec(@sql)

  SET @MovedtoNewLevel = @@Rowcount

  set @err='#ID''s yet to be processed :' + convert(varchar,@MovedtoNewLevel)

  --'BANDAID TO WORK AROUND CIRCULAR FK REFERENCES, WHERE EVENTUALLY,

  --TABLEA REFERS TO TABLEQ AND TABLEQ REFERS TO TABLEA

  if @LastBatch=@MovedtoNewLevel

  BEGIN

  select top 1 @err= TblName from #Hierarchy WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )

  set @err = 'possible Circular FK''s found, arbitrarily changing ' + @err + ' to break circular reference.'

  RAISERROR (@err,1,1)

  set rowcount 1

  UPDATE #Hierarchy SET FKLevel = FKLevel - 1

  WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )

  set rowcount 0

  END

  --RAISERROR(@ERR,1,1)

  SELECT @Level = @Level + 1

  End

  SELECT *

  FROM #Hierarchy

  order by FKLEVEL DESC

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• MRitch

  Ten Centuries

  Points: 1181

  More actions

  April 9, 2009 at 10:34 am

  #975214

  Sorry Lowell I always forget something... :Whistling:

  The tables are basically flatfiles. There is no FK or PK for that matter. They are a collection of denormalized data.

  For help on getting help, follow the link below:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 9, 2009 at 10:57 am

  #975237

  there you go then, no worries.

  about the only thing i could suggest is a lot of people here recommend creating a list of commands in a varchar(max) string and then executing that, instead of using any loop or cursor to do the work;(sp_MSforEachTable uses a cursor behind the schenes.)

  you can build a string or use the FOR XML shortcut to concatenate strings like that.

  declare @command varchar(max)

  SET @command=''

  select @command = @command + 'DROP TABLE ' + name + '; ' + CHAR(13) + CHAR(10) + 'PRINT ''TABLE ' + name + ' Dropped.''' + CHAR(13) + CHAR(10)

  FROM sys.objects A

  where type = 'U'

  AND IS_MS_SHIPPED = '0'

  AND DATEDIFF(DAY, CREATE_DATE, GETDATE()) > 60

  select @command

  --execute(@command)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• MRitch

  Ten Centuries

  Points: 1181

  More actions

  April 9, 2009 at 11:42 am

  #975302

  Thank you for your help! I will try your script.

  For help on getting help, follow the link below:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/