Archiving and Deleting Tables 1

[tEkHEd]

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..

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.

 

[pascalsql]

Search information_schema in BOL or MSDN

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'tblArchive%'

For future compatibility MS recommends to avoid looking in sysobjects.

You may have to use a cursor to act on each table, or some kind of table storing your archive names and a trigger to drop the old ones.

 

[tEkHEd]

hey pascalsql..

Thanks for your reply..

I have change the syntax from the sysobjects to INFORMATION_SCHEMA as you suggested, so thanks for that :¬)

I am unable to use a query such as you suggested to remove the obselete table as that would remove all tblArchiveXXX's

What I need to do is work out what the current date is, take away 60 days, and delete any tblArchive that has a number suffixed that is less than that..

I wanted to do something like (I know that this syntax is totally wrong..)

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME (60 < @TBLNAME)

any further ideas??

I suppose that I could create a master Archives table, with the arch table name and the date created, however I am still stuck with the same issue of being able to work out which tables to actually delete..

Thanks
º|º
=

 

[pascalsql]

Use substring to extract the prefix of your table.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'tblArchive%'
AND cast(SUSTRING(TABLE_NAME, 11, 3) as int) <= datepart(dy, getdate())-60

You still need a cursor...

declare @sql_query varchar(20)
DECLARE arc_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'tblArchive%'
AND cast(SUSTRING(TABLE_NAME, 11, 3) as int) <= datepart(dy, getdate())-60
OPEN arc_cursor
FETCH NEXT FROM arc_cursor  into @arc_table
WHILE @@FETCH_STATUS = 0
BEGIN
   select @sql_query = 'drop table ' + @arc_table
   execute(@sql_query)
   FETCH NEXT FROM arc_cursor into @arc_table
END
CLOSE arc_cursor
DEALLOCATE arc_cursor
GO

(note: I can't check the above syntax, I don't have sql server on my pc)

 

[tEkHEd]

pascalsql.. many many many thanks for this.. very useful

I had to make a couple of very minor adjustments to your code, but it was just declaring variables that you had missed, and I also needed to increase the length of the @SQL_Query var as it was a little too short (4 characters to be precise) and it cut off the end of the query. I also just changed the names a little..

But apart from that it worked like a charm have a star..

DECLARE @SQLDROP varchar(24), @DELARC varchar(13)
DECLARE ARCCURSOR CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'tblArchive%'
AND CAST(SUBSTRING(TABLE_NAME, 11, 3) AS INT) <= DATEPART(dy, GETDATE())-60
OPEN ARCCURSOR
FETCH NEXT FROM ARCCURSOR INTO @DELARC
WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @SQLDROP = 'DROP TABLE ' + @DELARC
   EXEC(@sql_query)
   --PRINT @DELARC
   --PRINT @SQLDROP
   FETCH NEXT FROM ARCCURSOR INTO @DELARC
END
CLOSE ARCCURSOR
DEALLOCATE ARCCURSOR

 

[tEkHEd]

OOOP's of course I have now pasted an error...

the EXEC line should be:

EXEC (@DELARC)

rather than

EXEC (@sql_query)