SQL Server programming - update all dates by a given number of days

Question

I have a demo database with a couple of hundred tables in it. Each table usually has at least one field named tstamp which is a smalldatetime datatype. Some tables have other datefields too. Many tables also have 1 or more triggers on them.

I wrote a script (the hard way - see below) to increment the date fields in each table by a given number of days. The idea is to make the data appear more "current" by updating all dates by the same amount of days.

I am sure there is an easier way to do this by looping over a system table to identify each user table in the database, disable all triggers on it, modify each smalldatetime field by adding the number of days to it, re-enabling the triggers and moving to the next table. I just do not have any idea how to write such T-SQL.

Any takers?

Thanks. Joe

Sample script:

DECLARE @numDaysToAdd int

SET @numDaysToAdd = 100

ALTER TABLE someTableDISABLE TRIGGER someTrigger

UPDATE someTable
SET tstamp = DATEADD(day, @numDaysToAdd, tstamp)

-- update any other smalldatetime field in the table too.

ALTER TABLE someTable ENABLE TRIGGER someTrigger

-- same pattern for 200 more tables!

========================================================================================== Omitting the trigger issue, here is a script that works:

DECLARE @numDaysToAdd int

SET @numDaysToAdd = 1

IF @numDaysToAdd > 0

BEGIN

DECLARE @tablename varchar(100)

DECLARE @currtable varchar(100)

DECLARE @currcolumn varchar(100)

DECLARE @columnname varchar(100)

DECLARE @strSQL nvarchar(4000)

DECLARE tnames_cursor CURSOR

FOR

SELECT t.TABLE_NAME, c.COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS c join INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE (c.DATA_TYPE = 'smalldatetime' OR c.DATA_TYPE = 'datetime') AND t.TABLE_TYPE<>'VIEW'

ORDER BY t.TABLE_NAME, c.COLUMN_NAME DESC

OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @tablename, @columnname

SET @currcolumn = @columnname

SET @currtable = @tablename

SET @strSQL = N'UPDATE ' + @tablename + CHAR(13)+CHAR(10) + 'SET ' + @columnname + ' = DATEADD(day, ' + CONVERT(varchar(10),@numDaysToAdd) + ', ' + @columnname + ')'

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF (@currtable = @tablename)

BEGIN     

  IF @currcolumn <> @columnname

    SET @strSQL = @strSQL + N',' + CHAR(13)+CHAR(10) + @columnname + ' = DATEADD(day, ' + CONVERT(varchar(10),@numDaysToAdd) + ', ' + @columnname + ')'
END

ELSE

BEGIN    

  SET @currtable = @tablename

  SET @currcolumn = @columnname

  EXEC sp_executesql @strSQL

  SET @strSQL = N'UPDATE ' + @tablename + CHAR(13)+CHAR(10) + 'SET ' + @columnname + ' = DATEADD(day, ' + CONVERT(varchar(10),@numDaysToAdd) + ', ' + @columnname + ')' 

END

FETCH NEXT FROM tnames_cursor INTO @tablename, @columnname

END

--run the final statement EXEC sp_executesql @strSQL

CLOSE tnames_cursor

DEALLOCATE tnames_cursor

END

Answer 1

Your understanding is correct. It sounds like the pieces you're missing are:

1. how to find metadata (what tables you've got, and what columns)
2. how to build up the SQL to walk over the tables.

For #1, see the system views INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS:

-- add your own additional criteria
select t.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c
join INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE = 'datetime'

For #2, you can build up a SQL statement as a string, walking through the tables you're interested in, then execute it with sp_executesql.