CODEWITHSUNDEEP
t-sqlsmalldatetime
Joe
Joe

Reputation:

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

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

Upvotes: 2

Views: 1990

Answers (3)

Arnkrishn
Arnkrishn

Reputation: 30434

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

Upvotes: 0

Cody C
Cody C

Reputation: 4807

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 :)

Upvotes: 0

Michael Petrotta
Michael Petrotta

Reputation: 60902

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.

Upvotes: 1

Related Questions