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