Finding last rundate table and delete from databas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding last rundate table and delete from databas

Author

Topic

sudheertheone
Starting Member

2 Posts

Posted - 2010-11-17 : 21:43:59

Hi i have 2 tables in database like bj_20101117 with todays rundate and the other table would be the date less than bj_20101117 (eg bj_20101116 or bj_20101115 or bj_20101114 etc....).

each time when i run the job i need to find the table less than todays rundate from database by comparing with todays run date
and delete it from database.

for if todays rundate table is bj_20101117 . the table that needs to be deleted could be like bj_20101116 or bj_20101115 etc..

i need a query for my requirement. could anybody help me..

regards
sudheer

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-11-18 : 01:52:43

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 'DROP TABLE ' + CAST(TABLE_NAME AS VARCHAR) + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'bj_%'
AND TABLE_NAME <> (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'bj_%' ORDER BY TABLE_NAME DESC)

EXEC (@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 06:24:50

quote:
Originally posted by waterduck

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 'DROP TABLE ' + CAST(TABLE_NAME AS VARCHAR) + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'bj_%'
AND TABLE_NAME <> (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'bj_%' ORDER BY TABLE_NAME DESC)

EXEC (@sql)

When you use cast or convert, make sure to specify the size for character datatypes
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 06:51:44

as per definition the last condition in WHERE can be just


AND TABLE_NAME <> 'bj_' + CONVERT(varchar(11),GETDATE(),112)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 10:14:01

quote:
Originally posted by visakh16

as per definition the last condition in WHERE can be just

AND TABLE_NAME <> 'bj_' + CONVERT(varchar(11),GETDATE(),112)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

8 is enough

AND TABLE_NAME <> 'bj_' + CONVERT(varchar(8),GETDATE(),112)

Madhivanan

Failing to plan is Planning to fail

sudheertheone
Starting Member

2 Posts

Posted - 2010-11-18 : 12:53:14

Thanks all of you guys for your response.
water duck your query worked for me

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-11-19 : 03:17:23

wow, thx alot madhivanan, noted, btw...izit only occur on char & varchar? i tried with (schar, varchar, nchar, nvarchar, text, ntext), seem only char family affected

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources