Calling Sql Dba Experts

[LallaayiPaata]

[color=#282828][font=helvetica, arial, sans-serif]Hi ma friend ki oka help kavali... Telsina vallu answer post cheyandi...[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]Here is the task...[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]We have 20 tables in a database... In each table we have data for 4-5 yrs back we shd write a script so that we can delete data for previous yrs and keep only 1 yr data in those tables[/font][/color]


[color=#282828][font=helvetica, arial, sans-serif]Thank you [/font][/color]

[karna11]

1st all 20 tabels must have similar createddate column? mundhu adhi vundhaaa?

[SQLbie]

LTT

[ghazni]

Do you want to delete related data? or do you want delete all the previous data from all the tables?

[LallaayiPaata]

[quote name='karna11' timestamp='1382109571' post='1304433886']
1st all 20 tabels must have similar createddate column? mundhu adhi vundhaaa?
[/quote]
Prathi table lo auditdate column undi....and aa 20 tables ki em relationshipslevu just primary key undi...

[LallaayiPaata]

[quote name='ghazni' timestamp='1382110020' post='1304433953']
Do you want to delete related data? or do you want delete all the previous data from all the tables?
[/quote]

[ghazni]

select c.name ,o.name
from sysobjects o
inner join syscolumns c
on
o.id=c.id
and
c.name='AUdit_column'
where o.type='u'
and o.uid=1

I think this should help to find all the columns that has audit column... based on this write a proc to dlete the respective data...

[LallaayiPaata]

We have to delete data for previous years and maintain only current yr data.. We shd write script to perform this task so that they can run it on regular bases... Please help

[ChampakDas]

endi vaa lallayi pata [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

[ChampakDas]

rough ga raskuntu poya....dint even execute in my machine if it runs wihtout error then it works else do tell me the error...tran statements peti chusko if this is working or not lekapote motham data kampu itadi..



declare @tablenames table(tablename varchar(200)) --ee table lo motham table names save chestunna
declare @deletestatement -- deentlo delete statement untadi..

insert into @tablenames(tablename)
SELECT name
FROM sys.Tables

select * from @tablenames

while(select top 1 @currenttablename = tablename from @tablenames)
begin
set @deletestatement = 'DELETE FROM' +@currenttablename+ 'WHERE auditdate < dateadd(yy,1,getdate())'
exec @deletestatement
delete from @tablenames where tablename = @currenttablename
end

[LallaayiPaata]

[quote name='GatisKandis' timestamp='1382111171' post='1304434104']
endi vaa lallayi pata [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]
[/quote]

Em ledu Gatis....Ma friend ki urgent ga help kavali ante post yesina.....[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img][img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

Thanks for the reply man...

Ma friend ki pampista nv rasina script...

[ChampakDas]

np..thanks nee degire petuko..[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img] edhem paata lallayi paata [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]
[quote name='LallaayiPaata' timestamp='1382111444' post='1304434131']
Em ledu Gatis....Ma friend ki urgent ga help kavali ante post yesina.....[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img][img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]

Thanks for the reply man...

Ma friend ki pampista nv rasina script...
[/quote]

[LallaayiPaata]

[quote name='GatisKandis' timestamp='1382111823' post='1304434182']
np..thanks nee degire petuko..[img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img] edhem paata lallayi paata [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]
[/quote]

Degire ndi [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]? Degree na...?

[ChampakDas]

thanks u keep with you man ani [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]
[quote name='LallaayiPaata' timestamp='1382112182' post='1304434229']
Degire ndi [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]? Degree na...?
[/quote]

[LallaayiPaata]

[quote name='GatisKandis' timestamp='1382111277' post='1304434117']
rough ga raskuntu poya....dint even execute in my machine if it runs wihtout error then it works else do tell me the error...tran statements peti chusko if this is working or not lekapote motham data kampu itadi..

Bhayya actually 20 tables ki sperate gaa rasyina like delete from tbl_audit_agjustment where auditdate < date add (yy,1,getdate()) run chesthe table lo motham table got deleted... Aa Where clause lo we shd change bhayya such that only date related to previous yr delete kavali current data undali

declare @tablenames table(tablename varchar(200)) --ee table lo motham table names save chestunna
declare @deletestatement -- deentlo delete statement untadi..

insert into @tablenames(tablename)
SELECT name
FROM sys.Tables

select * from @tablenames

while(select top 1 @currenttablename = tablename from @tablenames)
begin
set @deletestatement = 'DELETE FROM' +@currenttablename+ 'WHERE auditdate < dateadd(yy,1,getdate())'
exec @deletestatement
delete from @tablenames where tablename = @currenttablename
end
[/quote]