Unanswered: selecting tables that meet certain condition
I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.
So I just want to use a cursor on a table list that meet the criteria:
1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '
declare @strqry varchar(1000)
declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
fetch next from dailyYear into @DelTable
while @@FETCH_STATUS = 0
Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '
It's only a test DB not live so no problems if I mess it up. I need to delete a certain subset of data from all tables that meet the previous condition. However, because I'm searching with a column name it's also returning some tables I don't need. This isn't a major problem because when the delete command is run these tables I don't need won't match the criteria so no rows will be deleted.
condtition : qid >= 5000000 and qid < 1500000000 '
so really I only want tables returned that have a qid meeting this condition??
SELECT 'SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.Columns c WHERE COLUMN_NAME = ''OrderId'''
+ ' AND EXISTS (SELECT COUNT(*) FROM ['
+ TABLE_NAME + '] WHERE c.TABLE_NAME = '
+ TABLE_NAME + ' AND OrderId > 10250 AND Order < 10260'
WHERE TABLE_NAME IN (SELECT DISTINCT TABLE_NAME
WHERE COLUMN_NAME = 'OrderId')