Script zur Überprüfung individueller Zusatzfelder (siehe Umsteller-Checkliste Kapitel 2.3.1)

Bitte beachten Sie die folgenden Hinweise zur Ausführung von SQL-Anweisungen:

1. Stellen Sie sicher, dass vor Durchführung der Korrekturanweisungen eine aktuelle Datensicherung Ihres Datenbestandes vorliegt.

2. Stellen Sie sicher, dass die SQL-Anweisung nur durch einen Anwender ausgeführt wird, der sich über den Sachverhalt im Klaren ist.

3. Stellen Sie sicher, dass während der Ausführung der SQL-Anweisung kein Benutzer in dem zu ändernden Mandanten angemeldet ist.

4. Die Abarbeitung der SQL-Anweisung kann je nach Volumen des Datenbestandes einige Zeit in Anspruch nehmen.

5. Prüfen Sie nach Ausführung der SQL-Anweisung, ob das gewünschte Ergebnis eingetreten ist.

/**_______________________________________________________________________

Dieses Script vergleicht die Konfiguration von Zusatzfeldern mit dem
Datenbank Schema. Datenbankfelder, die nicht der Konfiguration
entsprechen, werden entsprechend aufgelistet.

Folgende Fehler werden geprüft:

- Felder die es nicht in der Datenbank gibt
- Felder deren Datentyp nicht mit der Datenbank übereinstimmt
- Felder deren Länge nicht mit der Datenbank übereinstimmt

!!! A U F S Y S T E M D A T E N B A N K A U S F Ü H R E N !!!

_____________________________________________________________________**/

SET NOCOUNT ON

DECLARE @servername SYSNAME = CAST(SERVERPROPERTY('ServerName') AS sysname);
DECLARE @servercollation SYSNAME = CAST(SERVERPROPERTY('Collation') AS sysname);

DECLARE
@companyid INT,
@dbserver VARCHAR(30),
@dbname VARCHAR(20)

DECLARE @tblResult TABLE (
ServerName SYSNAME NOT NULL,
DbName SYSNAME NOT NULL,
TableName NVARCHAR(50) NOT NULL,
ColumnName NVARCHAR(50) NOT NULL,
FieldType NVARCHAR(30) NULL,
DataType SYSNAME NULL,
ColumnLength INT NULL,
ExistsInSchema BIT NOT NULL,
SchemaDataType SYSNAME NULL,
SchemaLength INT NULL,
PRIMARY KEY (ServerName, DbName, TableName, ColumnName)
)

-- alle Datenbanken aus dem Firmenverzeichnis auslesen
DECLARE dbCur CURSOR FAST_FORWARD FOR
SELECT
Firmennummer,
RTRIM(Datenbankserver),
RTRIM(Datenbankname)

FROM
Firmenverzeichnis

OPEN dbCur
FETCH NEXT FROM dbCur INTO @companyid, @dbserver, @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

IF (UPPER(@dbserver) != UPPER(CAST(SERVERPROPERTY('ServerName') AS sysname))) PRINT 'Die Datenbank ' + QUOTENAME(@dbName) + ' kann nicht geprüft werden, da sie auf einem anderen Datenbankserver liegt (Server: ' + QUOTENAME(@dbserver) + ').'
ELSE IF (NOT EXISTS(SELECT * FROM master..sysdatabases WHERE name = @dbname)) PRINT 'Die Datenbank ' + QUOTENAME(@dbName) + ' kann nicht geprüft werden, da sie nicht auf dem Datenbankserver verfügbar ist (Server: ' + QUOTENAME(@dbserver) + ').'
ELSE BEGIN
PRINT 'Die Datenbank ' + QUOTENAME(@dbName) + ' wird geprüft.'

DECLARE @sql NVARCHAR(4000) = '-- Zusatzfeldkonfiguration in einzelne Datensätze teilen
DECLARE @tblAdditonalFields TABLE (
RowNumber INT NOT NULL IDENTITY(1,1),
TableName SYSNAME NOT NULL,
Field NVARCHAR(max) NOT NULL
);

INSERT INTO @tblAdditonalFields (TableName, Field)
SELECT DISTINCT
Tabelle,
value AS Field

FROM
' + QUOTENAME(@dbname) + '..Zusatz

CROSS APPLY STRING_SPLIT(CAST(Felder AS NVARCHAR(max)), char(10))

WHERE Felder IS NOT NULL
AND value != ''*''
AND ISNULL(LTRIM(RTRIM(value)), '''') != '''';

-- Feldkonfiguration in einzelne Spalten aufteilen
DECLARE @tblAdditonalFieldSchema TABLE (
TableName SYSNAME NOT NULL,
ColumnName SYSNAME NULL,
FieldType SYSNAME NULL,
DataType SYSNAME NULL,
Length INT NULL
);

WITH FN
AS (
SELECT
RowNumber,
TableName,
value,
ROW_NUMBER() OVER(PARTITION BY RowNumber ORDER BY (SELECT NULL)) AS FieldIndex

FROM
@tblAdditonalFields Fields

CROSS APPLY STRING_SPLIT(Field, '','') AS BK
)

INSERT INTO @tblAdditonalFieldSchema
SELECT DISTINCT
RTRIM(TableName),
''ZF'' + RTRIM(LTRIM([8])) AS FieldName,
SUBSTRING([1], CHARINDEX('''''''',[1] , 2) + 1, 255 ) AS FieldType,
CASE SUBSTRING([1], CHARINDEX('''''''',[1] , 2) + 1, 255 )
WHEN ''INT'' THEN ''int''
WHEN ''STRING'' THEN ''char''
WHEN ''BETRAG'' THEN ''money''
WHEN ''DATUM'' THEN ''smalldatetime''
WHEN ''ZEIT'' THEN ''datetime''
WHEN ''POP'' THEN ''char''
WHEN ''EDIPOP'' THEN ''char''
WHEN ''POPKZ'' THEN ''char''
WHEN ''CHECK'' THEN ''int''
END AS DataType,
[2] AS Length

FROM
FN

PIVOT( MAX(VALUE) FOR FieldIndex IN([1],[2],[3],[4],[5],[6],[7],[8])

) as PVT

SELECT
@dbserver AS ServerName,
@dbname AS DatabaseName,

AddlFields.TableName,
AddlFields.ColumnName,
AddlFields.FieldType,
AddlFields.DataType,
AddlFields.Length,

CASE WHEN Cols.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS ExistsInSchema,
Cols.DATA_TYPE AS SchemaDataType,
Cols.CHARACTER_MAXIMUM_LENGTH AS SchemaLength

FROM
@tblAdditonalFieldSchema AddlFields

LEFT JOIN ' + QUOTENAME(@dbname) + '.INFORMATION_SCHEMA.COLUMNS Cols
ON (AddlFields.TableName = Cols.TABLE_NAME collate ' + @servercollation + ')
AND (AddlFields.ColumnName = Cols.COLUMN_NAME collate ' + @servercollation + ')';

INSERT INTO @tblResult
EXEC sp_executesql @sql , N'@dbname VARCHAR(20), @dbserver VARCHAR(30)', @dbname = @dbname, @dbserver = @dbserver;
END

FETCH NEXT FROM dbCur INTO @companyid, @dbserver, @dbname

END

CLOSE dbCur
DEALLOCATE dbcur

PRINT '1) Felder die es nicht in der Datenbank gibt'
SELECT
ServerName,
DbName,
TableName,
ColumnName AS Felder_die_nicht_in_der_Datenbank_existieren

FROM
@tblResult

WHERE
ExistsInSchema = 0

PRINT '2) Felder mit falschem Datentyp'
SELECT
ServerName,
DbName,
TableName,
ColumnName AS Felder_mit_falschem_Datentyp,
FieldType AS Feldtyp_in_der_Konfiguration,
DataType AS Datentyp_in_der_Konfiguration,
SchemaDataType AS Datentyp_in_der_Datenbank
FROM
@tblResult

WHERE
DataType != SchemaDataType