SQL Server-Programmierung - Aktualisierung aller Daten um eine bestimmte Anzahl von Tagen

Question

Ich habe eine Demo-Datenbank mit ein paar hundert Tabellen. Jede Tabelle hat normalerweise mindestens ein Feld mit dem Namen tstamp, das ein Smalldatetime-Datentyp ist. Einige Tabellen haben auch andere Datumsfelder. Viele Tabellen haben auch 1 oder mehrere Trigger auf sie.

Ich habe ein Skript geschrieben (auf die harte Tour - siehe unten), um die Datumsfelder in jeder Tabelle um eine bestimmte Anzahl von Tagen zu erhöhen. Die Idee ist, die Daten "aktueller" erscheinen zu lassen, indem alle Daten um die gleiche Anzahl von Tagen aktualisiert werden.

Ich bin mir sicher, dass es einen einfacheren Weg gibt, dies zu tun, indem man eine Schleife über eine Systemtabelle macht, um jede Benutzertabelle in der Datenbank zu identifizieren, alle Trigger darauf zu deaktivieren, jedes smalldatetime-Feld zu ändern, indem man die Anzahl der Tage hinzufügt, die Trigger wieder zu aktivieren und zur nächsten Tabelle zu gehen. Ich habe einfach keine Ahnung, wie ich solche T-SQL schreiben soll.

Hat jemand Lust?

Danke! Joe

Beispielskript:

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!

\========================================================================================== Lassen Sie das Problem mit dem Auslöser beiseite, hier ist ein Skript, das funktioniert:

DECLARE @numDaysToAdd int

SET @numDaysToAdd = 1

WENN @numDaysToAdd > 0

BEGIN

DECLARE @tabellenname varchar(100)

DECLARE @currtable varchar(100)

DECLARE @currcolumn varchar(100)

DECLARE @Spaltenname varchar(100)

DECLARE @strSQL nvarchar(4000)

DECLARE tnames_cursor CURSOR

FÜR

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 @tablenname, @spaltenname

SET @currcolumn = @columnname

SET @currtable = @tablenname

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

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF (@currtable = @tablesame)

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 @tablenname, @spaltenname

ENDE

-die Endabrechnung ausführen EXEC sp_executesql @strSQL

CLOSE tnames_cursor

DEALLOCATE tnames_cursor

ENDE

Answer 1

Ihr Verständnis ist richtig. Es klingt so, als ob die Teile, die Sie vermissen, sind:

1. wie man Metadaten findet (welche Tabellen Sie haben, und welche Spalten)
2. wie man den SQL aufbaut, um darüber zu gehen die Tabellen.

Für #1, siehe die Systemansichten INFORMATION_SCHEMA.TABLES y 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'

Für #2 können Sie eine SQL-Anweisung als Zeichenkette erstellen, die durch die Tabellen geht, an denen Sie interessiert sind, und sie dann ausführen mit sp_executesql .

Answer 2

Ich bin einverstanden. Eine andere Möglichkeit ist die Verwendung von Systemtabellen, um das Sql für alle 200 Tabellen zu generieren. Sie können dann sp_execsql zur Ausführung verwenden. Das ändert nichts an der Ausführung, erspart Ihnen aber Tipparbeit, was immer wichtig ist :)

Answer 3

Mit der folgenden Abfrage erhalten Sie die Liste der Benutzertabellen und ihrer Spalten vom Typ "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

hier ist 58 system_type_id für den Datentyp smallDateTime. Sie können dies anhand der Tabelle sys.types überprüfen.

Mit einem Cursor können Sie über die Ergebnismenge iterieren, um jede Tabelle zu erhalten und dann Trigger für diese Tabelle zu deaktivieren. Prüfen Sie dies für Trigger deaktivieren/aktivieren http://msdn.microsoft.com/en-us/library/ms189748.aspx

Aktualisieren Sie dann jede Spalte in der Ergebnismenge für jede Tabelle und aktivieren Sie anschließend die Auslöser.

Prost