Is sp_MSforeachtable safe in this instance

Question

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/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

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: 323518 More actions · Answer 3

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 · Answer 4

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/