如何迭代数据库中的表格，然后基于条件对这些表格行进行迭代？

Question

我正在通过AdventureWorks2008R2数据库工作，并试图找出是否有一种方法来遍历此数据库中的所有表，然后给出一个条件遍历该表的特定列中的行？

目前我有

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%' 
; 

我想什么从这里做的就是去通过这些表，然后他们行改变基于给定条件的日期。

在非SQL代码和适度宽松的伪它可能是这样的

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 

任何人都可以帮助吗？

在此先感谢！

UPDATE

这里是我创建从@Mars解决方案适应它的代码。

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<[email protected]) 
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 [email protected] 
    EXEC (@query) 
    SET @start+=1 
END 

DROP TABLE #temptable 

Answer 1

您需要设置所需的日期更新和条件检查到变量@updatatingDate和[通过所有SQL表如何循环]的@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<[email protected]) 
BEGIN 
    SELECT 
    @query= 'UPDATE '+TABLE_SCHEMA+'.' 
      +TABLE_NAME +' SET '+ COLUMN_NAME 
      +' ='''[email protected]+''' WHERE ' 
      [email protected] 
    FROM #temptable 
    WHERE SN [email protected] 
    EXEC (@query) 
    SET @start+=1 
END 
--DROP TABLE #temptable;