How do I iterate through tables in a database then those tables rows based on a condition?

Question

I'm working through AdventureWorks2008R2 database and I'm trying to figure out if there is a way to iterate through all of the tables within this database and then given a condition iterate through the rows within a specific column of that table?

Currently I have

SELECT DISTINCT
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE ('%date%') AND COLUMN_NAME Not LIKE '%JobCandidate%'
;

What I'd like to do from here is go through these tables and then their rows changing the the dates based on a given condition.

In non SQL code and loose pseudo it might be something like

FOR TABLES 
SELECT A TABLE WITH A COLUMN THAT HAS A DATE IN IT 
FOR COLUMN_WITH_DATE_HEADER
IF ROW HAS DATE GIVEN SOME CONDITION 
UPDATE ROW IN THAT TABLE

Can anyone assist with this?

Thanks in advance!

UPDATE

Here is the code that I created adapting it from @Mars solution.

SELECT DISTINCT
IDENTITY(int,1,1) AS SN,
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
 , TABLE_SCHEMA
 , TABLE_NAME
 , COLUMN_NAME
INTO #temptable
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE ('%date%') 
AND COLUMN_NAME Not LIKE '%JobCandidate%' AND 
COLUMN_NAME NOT LIKE '%BirthDate%';

  DECLARE @start INT=1
, @end INT=0
, @query NVARCHAR(MAX)='';
DECLARE @conditions NVARCHAR(MAX)='' ;


SELECT @end=MAX(SN)
FROM #temptable
WHILE (@start<=@end)
BEGIN
    SELECT  
    @conditions = ' WHERE ' + 'YEAR('+ COLUMN_NAME +')' + ' < 2009',
    @query= 'UPDATE '+TABLE_SCHEMA+'.' 
            +TABLE_NAME +' SET '+ COLUMN_NAME
            +' = dateadd(YEAR,8,' + COLUMN_NAME + ')'      
    FROM #temptable
    WHERE SN =@start
    EXEC (@query)
    SET @start+=1
END

DROP TABLE #temptable

Possible duplicate of [How to loop through all SQL tables](http://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables) — STLDev, Apr 03 '17 at 22:00

Mars · Accepted Answer · 2017-04-04T08:16:05.287

You need to set required date to update and conditions to check into variables @updatatingDate and @conditions

SELECT DISTINCT
IDENTITY(int,1,1) AS SN,
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
 , TABLE_SCHEMA
 , TABLE_NAME
 , COLUMN_NAME
INTO #temptable
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE ('%date%') 
AND COLUMN_NAME Not LIKE '%JobCandidate%';

  DECLARE @start INT=1
, @end INT=0
, @query NVARCHAR(MAX)=''
, @updatatingDate NVARCHAR(100)='2017-04-04'
, @conditions NVARCHAR(MAX)='1=1'

SELECT @end=MAX(SN)
FROM #temptable
WHILE (@start<=@end)
BEGIN
    SELECT 
    @query= 'UPDATE '+TABLE_SCHEMA+'.' 
            +TABLE_NAME +' SET '+ COLUMN_NAME
            +' ='''+@updatatingDate+''' WHERE '
            +@conditions
    FROM #temptable
    WHERE SN =@start
    EXEC (@query)
    SET @start+=1
END
--DROP TABLE #temptable;

Hello Mars, thank you for your solution. I've been working with it these past few days to understand what you've done and it is exactly what I was looking for. It's a great approach and I appreciate the ability to modify it as needed for more complex iterative queries.Many thanks! — user2023068, Apr 06 '17 at 14:48

How do I iterate through tables in a database then those tables rows based on a condition?

1 Answers1