asked
Search for table names with date in the name
Hi, I have some tables with names like: table20130301 table20130302 table20130303 and so on, one table for each day. I need to extract this date from the table name. then this date is to be compared to know if it is older than 90 days ..and then some archive process is to be carried out. I tried this for one day and I think it works... DECLARE @a varchar(100),@count int,@temp int, @archive_days int,@table_days int,@tbl_name varchar(100),@dt_tbl_name datetime,@dt_days int set @archive_days = 90 select top @tbl_name = name FROM test1.sys.tables where type = 'u' and name like 'table%' print @tbl_name set @dt_tbl_name = RIGHT ('' + @tbl_name + '',8) --print @dt_tbl_name set @dt_days = datediff (dd,@dt_tbl_name,getdate()) print @dt_days but the problem is, the above code will run through all the tables which start with 'table%'..is this the only way to loop though the tables or is there a simpler way? any help is highly appreciated!!! thanks...
Can you use the date the table was created instead of the name? If so, use this : select top 1 @tbl_name = name FROM test1.sys.tables where type = 'u' and name like 'table[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and create_date < dateadd(day,0-@archive_days,CAST(getdate() as DATE)) If you have to use the name, try this : select top 1 @tbl_name = name FROM test1.sys.tables where type = 'u' and name like 'table[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and name
13 People are following this question.
