如何从数据库中的表中删除所有数据，其中YEAR（AddedDt）<'2011' [英] How to delete all data from tables in a database where YEAR(AddedDt)<'2011'

问题描述

我有一个200和一些表的数据库，bcoz由于某种原因我想删除表中YEAR（AddedDt）<'2011'的所有数据，甚至只记录那些记录数量> 1000的表。





可选： -

1）我已经写了一个查询来获取那些column_name就像'AddedDT'

2）现在所有表格都粘贴在excel中，并且通过使用连接函数我可以做到。



##但我想要的是有通过仅使用sql来实现这一目的的任何技巧/方法。？



谢谢，

I have one database with 200 and something tables, bcoz of some reason I want to delete all data from tables where YEAR(AddedDt)<'2011' and even only those tables where recordcount >1000.


Optionally:-
1)I have already written a query to get those table where column_name like 'AddedDT'
2)Now all tables pasted in excel and by using concatenate function i can do it.

##But i want to is there is any trick/approach to do this by using only sql.?

Thanks,

♣ ≈Ω°±Σⁿ 

推荐答案

从（选择*来自INFORMATION_SCHEMA.COLUMNS，其中column_name ='AddedDT'）t和Year（t.addedDt）< ; 2011



你可以试试这个，希望这会对你有所帮助......

Delete from (Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'AddedDT') t and Year(t.addedDt)<2011

You can try this, hope this will helps you...

嗨请试试这个

Hi Please try this

declare @ptmp table(tblname varchar(500),row bigint)
insert into @ptmp
select IC.Table_catalog+'.'+IC.TAble_schema+'.'+IC.table_name Tname,max(SS.rows) Rws from information_schema.columns IC
 inner join sysindexes SS on IC.table_NAMe=object_name(SS.id)  where column_name='AddedDt'
group by  IC.Table_catalog,IC.table_Schema,IC.table_name
 
declare @ptablename varchar(40)
select * from @ptmp
declare cur_temp cursor
	for select tblname from @ptmp where row >1000
open cur_temp 
	fetch next from cur_temp into @ptablename
print 'delete from ' + @ptablename + ' where year(addedDt)<2011 '

 CLOSE cur_temp
    DEALLOCATE cur_temp

这篇关于如何从数据库中的表中删除所有数据，其中YEAR（AddedDt）&lt;'2011'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！