Hi..
I have a series of archived tables called tblArchiveXXX where XXX is the Day of Year.
I want to write into the sproc that creates the archive table a routine that will also check the schema for any tables prefixed with tblArchive and have a Day of Year more than 60 days of the latest archive table and delete them..
Can anyone help with this?
here is how the archive tables are being created..
Or perhaps someone knows of a better function for this??
I would also like to include error handling into this and other scripts but am new to SQL so am unsure how to use the @@ERROR var, and how it output's, so also if there are any pointers on that, I would be very grateful..
Thanks in advance.
I have a series of archived tables called tblArchiveXXX where XXX is the Day of Year.
I want to write into the sproc that creates the archive table a routine that will also check the schema for any tables prefixed with tblArchive and have a Day of Year more than 60 days of the latest archive table and delete them..
Can anyone help with this?
here is how the archive tables are being created..
Code:
DECLARE -- Declare Vars
@CREATEARC AS VARCHAR(255), @DROPARC AS VARCHAR(255), @DROPSW AS VARCHAR(255),
@DOY AS CHAR(3), @TBLNAME AS CHAR(13)
-- Setup the Archive Table Name
SET @DOY = (SELECT DATEPART(dy, GETDATE()))
SET @TBLNAME = 'tblArchive'+@DOY
-- Setup SQL Strings
SET @DROPARC = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N' + '''[' + @TBLNAME + ']'') AND OBJECTPROPERTY(id, N' + '''IsUserTable''' + ') = 1) DROP TABLE [' + @TBLNAME + ']'
SET @CREATEARC = 'SELECT * INTO dbo.' + @TBLNAME + ' FROM tblSWInst'
SET @DROPSW = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N' + '''[tblSWInst]''' + ') AND OBJECTPROPERTY(id, N' + '' + '''IsUserTable''' + ')=1) DROP TABLE tblSWInst'
-- Run SQL
EXEC(@DROPARC) -- If exists, drops existing archive (archive would have had to have been created same day)
EXEC(@CREATEARC) -- Creates the Archive Table
EXEC(@DROPSW) -- Deletes existing tblSWInst
-- End of Procedure --
Or perhaps someone knows of a better function for this??
I would also like to include error handling into this and other scripts but am new to SQL so am unsure how to use the @@ERROR var, and how it output's, so also if there are any pointers on that, I would be very grateful..
Thanks in advance.