Программирование SQL Server - обновление всех дат на заданное количество дней

У меня есть демонстрационная база данных с парой сотен таблиц. Каждая таблица обычно имеет по крайней мере одно поле с именем tstamp, которое является типом данных smalldatetime. В некоторых таблицах есть и другие поля даты. На многих таблицах также есть один или несколько триггеров.

Я написал сценарий (сложный способ - см. Ниже) для увеличения полей даты в каждой таблице на заданное количество дней. Идея состоит в том, чтобы сделать данные более «актуальными», обновив все даты на одинаковое количество дней.

Я уверен, что есть более простой способ сделать это, перебрав системную таблицу, чтобы идентифицировать каждую пользовательскую таблицу в базе данных, отключить все триггеры на ней, изменить каждое поле smalldatetime, добавив к нему количество дней, повторно включив триггеры и переход к следующей таблице. Я просто не знаю, как написать такой T-SQL.

Есть берущие?

Спасибо. Джо

Пример сценария:

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!

================================================== ======================================== Опуская проблему триггера, вот сценарий, который работает:

ОБЪЯВИТЬ @numDaysToAdd int

НАБОР @numDaysToAdd = 1

ЕСЛИ @numDaysToAdd> 0

НАЧИНАТЬ

ОБЪЯВЛЕНИЕ @tablename varchar (100)

ОБЪЯВИТЬ @currtable varchar (100)

ОБЪЯВИТЬ @currcolumn varchar (100)

ОБЪЯВИТЬ @columnname varchar (100)

ОБЪЯВИТЬ @strSQL nvarchar (4000)

ОБЪЯВЛЕНИЕ КУРСОРА tnames_cursor

ЗА

ВЫБЕРИТЕ t.TABLE_NAME, c.COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS c присоединиться к INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

ГДЕ (c.DATA_TYPE = 'smalldatetime' ИЛИ ​​c.DATA_TYPE = 'datetime') И t.TABLE_TYPE ‹> 'ПРОСМОТР'

ЗАКАЗАТЬ t.TABLE_NAME, c.COLUMN_NAME DESC

ОТКРЫТЬ tnames_cursor

ВЫБРАТЬ ДАЛЕЕ ИЗ tnames_cursor В @tablename, @columnname

НАБОР @currcolumn = @columnname

УСТАНОВИТЬ @currtable = @tablename

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

ПОКА (@@ FETCH_STATUS = 0)

НАЧИНАТЬ

ЕСЛИ (@currtable = @tablename)

BEGIN     

  IF @currcolumn <> @columnname

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

ЕЩЕ

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

ВЫБРАТЬ ДАЛЕЕ ИЗ tnames_cursor В @tablename, @columnname

КОНЕЦ

- выполнить последний оператор EXEC sp_executesql @strSQL

ЗАКРЫТЬ tnames_cursor

DEALLOCATE tnames_cursor

КОНЕЦ


tsql smalldatetime
person Community    schedule 02.07.2009    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Ваше понимание правильное. Похоже, что вам не хватает:

  1. как найти метаданные (какие у вас есть таблицы и какие столбцы)
  2. как построить SQL для обхода таблиц.

Для №1 см. Системные представления INFORMATION_SCHEMA.TABLES и 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'

Для №2 вы можете создать оператор SQL в виде строки, пройдя по интересующим вас таблицам, а затем выполнить его с помощью sp_executesql.

person Michael Petrotta    schedule 02.07.2009

arrow_upward
0
arrow_downward

Я согласен. Другой вариант - использовать системные таблицы для создания sql для всех 200 таблиц. Затем вы можете использовать sp_execsql для exec. Не изменит исполнение, но избавит вас от набора текста, что всегда важно :)

person Cody C    schedule 02.07.2009

arrow_upward
0
arrow_downward

Следующий запрос предоставит вам список пользовательских таблиц и их столбцов, которые имеют тип 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

здесь 58 - это system_type_id для типа данных smallDateTime. Вы можете проверить это из таблицы sys.types.

Используя курсор, вы можете перебирать набор результатов, чтобы получить каждую таблицу, а затем отключить триггеры в этой таблице. Установите этот флажок для отключения / включения триггера http://msdn.microsoft.com/en-us/library/ms189748.aspx

Затем обновите каждый столбец в наборе результатов, относящийся к каждой таблице, а затем активируйте триггеры.

ваше здоровье

person Arnkrishn    schedule 02.07.2009