views:

272

answers:

5

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

A: 

That is exactly how I would do it. I am not sure there is a more elegant way.

William Edmondson
A: 

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.

Michael Petrotta
A: 

I agree. Another option is to use system tables to generate the sql for all 200 tables. You can then use sp_execsql to exec. Won't change execution but will save you typing, which is always important :)

Cody C
A: 

For the triggers, see my question here.

Austin Salonen
A: 

Hi

The following query would give you the list of user tables and their columns which are of type 'smallDateTime'.

SELECT sys.columns.name as tableName, sys.tables.name as columnName from sys.columns,sys.tables 
where sys.columns.object_id=sys.tables.object_id and sys.columns.system_type_id=58 order by tableName

here 58 is system_type_id for data type - smallDateTime. You can verify it from sys.types table.

Using a cursor may be you can iterate over the result set to get each table and then disable triggers on that table. Check this for trigger disable/enable http://msdn.microsoft.com/en-us/library/ms189748.aspx

Then go ahead and update each column in the result set pertaining to each table, followed by enabling the triggers.

cheers

Andriyev