USE [DWH_Meta]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.[DW_ETL_Objects_detail]', 'U') IS NOT NULL
DROP TABLE dbo.[DW_ETL_Objects_detail];
CREATE TABLE [dbo].[DW_ETL_Objects_detail](
[TABLE_CATALOG] [nvarchar](120) NOT NULL,
[TABLE_SCHEMA] [nvarchar](50) NOT NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NOT NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](255) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL,
[Valid from] [datetime] NOT NULL,
[Date of Expiry] [datetime] NOT NULL,
CONSTRAINT [PK_DW_ETL_Objects_detail] PRIMARY KEY CLUSTERED
(
[TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME],[Valid from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DW_ETL_Objects_detail] ADD DEFAULT (getdate()) FOR [Valid from]
GO
ALTER TABLE [dbo].[DW_ETL_Objects_detail] ADD DEFAULT ('01/01/2099') FOR [Date of Expiry]
GO
IF OBJECT_ID('dbo.[DW_ETL_Objects]', 'U') IS NOT NULL
DROP TABLE dbo.[DW_ETL_Objects];
CREATE TABLE [dbo].[DW_ETL_Objects](
[QuellDatabase] [varchar](255) NOT NULL,
[ObjectTYPE] [varchar](5) NOT NULL,
[ObjectName] [sysname] NOT NULL,
[ObjectDefinition] [ntext] NULL,
[Valid from] [datetime] NOT NULL,
[Date of Expiry] [datetime] NOT NULL,
CONSTRAINT [PK_DW_ETL_Objects] PRIMARY KEY CLUSTERED
(
[QuellDatabase],[ObjectTYPE],[ObjectName],[Valid from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DW_ETL_Objects] ADD DEFAULT (getdate()) FOR [Valid from]
GO
ALTER TABLE [dbo].[DW_ETL_Objects] ADD DEFAULT ('01/01/2099') FOR [Date of Expiry]
GO
Um diese Tabellen zu füllen, erstellen wir folgende PROCEDURE:
USE [DWH_Meta]
GO
/****** Object: StoredProcedure [dbo].[MetaRefresh] Script Date: 11.05.2021 07:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[MetaRefresh]
-- Add the parameters for the stored procedure here
AS
DECLARE @TSQLScript NVARCHAR(max)
DECLARE @Databases SYSNAME
DECLARE cUpdateStatistics CURSOR READ_ONLY FOR
select name from sys.databases
WHERE name not In ( 'master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
CREATE TABLE #xHelper_Objects
(
[QuellDatabase] [varchar](255) NOT NULL,
[ObjectTYPE] [varchar](50) NOT NULL,
[ObjectName] [sysname] NOT NULL,
[ObjectDefinition] [ntext] NULL
);
CREATE TABLE #xHelper_Objects_detail
(
[TABLE_CATALOG] [nvarchar](120) NOT NULL,
[TABLE_SCHEMA] [nvarchar](50) NOT NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NOT NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](255) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
);
OPEN cUpdateStatistics
FETCH NEXT FROM cUpdateStatistics INTO @Databases
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TSQLScript = 'insert into #xHelper_Objects_detail
SELECT * FROM '+ QUOTENAME(@Databases) +'.Information_Schema.Columns with (nolock) ORDER BY [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME]'
exec sp_executesql @TSQLScript
SELECT @TSQLScript = 'insert into #xHelper_Objects ([QuellDatabase],[ObjectTYPE],[ObjectName],[ObjectDefinition])
SELECT '''+ QUOTENAME(@Databases) +''' AS QuellDatabase,
o.type_desc AS ObjectTYPE, OBJECT_NAME(sm.object_id) as ObjectName,
cast(sm.definition as ntext) as ObjectDefinition
FROM '+ QUOTENAME(@Databases) +'.sys.sql_modules AS sm with (nolock)
JOIN '+ QUOTENAME(@Databases) +'.sys.objects AS o with (nolock) ON sm.object_id = o.object_id
WHERE o.type IN (''P '', ''V'') AND OBJECT_NAME(sm.object_id) IS NOT NULL
UNION ALL
SELECT '''+ QUOTENAME(@Databases) +''' AS QuellDatabase,
''Table'' AS ObjectTYPE ,TABLE_NAME as ObjectName,
'''' as ObjectDefinition
FROM '+ QUOTENAME(@Databases) +'.INFORMATION_SCHEMA.TABLES with (nolock)
where TABLE_TYPE = ''BASE TABLE'' ORDER BY [QuellDatabase],[ObjectTYPE],[ObjectName]'
exec sp_executesql @TSQLScript
FETCH NEXT FROM cUpdateStatistics INTO @Databases
END
---DW_ETL_Objects_detail
MERGE [DWH_Meta].[dbo].[DW_ETL_Objects_detail] AS TARGET
USING #xHelper_Objects_detail AS SOURCE
ON (
TARGET.[TABLE_CATALOG]= SOURCE.[TABLE_CATALOG]
AND TARGET.[TABLE_SCHEMA]= SOURCE.[TABLE_SCHEMA]
AND TARGET.[TABLE_NAME]= SOURCE.[TABLE_NAME]
AND TARGET.[COLUMN_NAME]= SOURCE.[COLUMN_NAME]
AND TARGET.[ORDINAL_POSITION]= SOURCE.[ORDINAL_POSITION] AND TARGET.[Date of Expiry] = '01.01.2099')
WHEN MATCHED AND TARGET.[COLUMN_DEFAULT] <> SOURCE.[COLUMN_DEFAULT] OR TARGET.[IS_NULLABLE] <> SOURCE.[IS_NULLABLE] OR TARGET.[DATA_TYPE] <> SOURCE.[DATA_TYPE] OR TARGET.[CHARACTER_MAXIMUM_LENGTH] <> SOURCE.[CHARACTER_MAXIMUM_LENGTH]
OR TARGET.[CHARACTER_OCTET_LENGTH] <> SOURCE.[CHARACTER_OCTET_LENGTH] OR TARGET.[NUMERIC_PRECISION] <> SOURCE.[NUMERIC_PRECISION] OR TARGET.[NUMERIC_PRECISION_RADIX] <> SOURCE.[NUMERIC_PRECISION_RADIX]
OR TARGET.[NUMERIC_SCALE] <> SOURCE.[NUMERIC_SCALE] OR TARGET.[DATETIME_PRECISION] <> SOURCE.[DATETIME_PRECISION] OR TARGET.[CHARACTER_SET_CATALOG] <> SOURCE.[CHARACTER_SET_CATALOG] OR TARGET.[CHARACTER_SET_SCHEMA] <> SOURCE.[CHARACTER_SET_SCHEMA] OR TARGET.[CHARACTER_SET_NAME] <> SOURCE.[CHARACTER_SET_NAME] OR TARGET.[COLLATION_CATALOG] <> SOURCE.[COLLATION_CATALOG]
OR TARGET.[COLLATION_SCHEMA] <> SOURCE.[COLLATION_SCHEMA] OR TARGET.[COLLATION_NAME] <> SOURCE.[COLLATION_NAME] OR TARGET.[DOMAIN_CATALOG] <> SOURCE.[DOMAIN_CATALOG] OR TARGET.[DOMAIN_SCHEMA] <> SOURCE.[DOMAIN_SCHEMA] OR TARGET.[DOMAIN_NAME] <> SOURCE.[DOMAIN_NAME]
THEN UPDATE SET TARGET.[Date of Expiry] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[Date of Expiry] = '01.01.2099' THEN UPDATE SET TARGET.[Date of Expiry] = getdate()
WHEN NOT MATCHED BY TARGET
THEN INSERT ([TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[COLUMN_NAME]
,[ORDINAL_POSITION]
,[COLUMN_DEFAULT]
,[IS_NULLABLE]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[DOMAIN_CATALOG]
,[DOMAIN_SCHEMA]
,[DOMAIN_NAME]) VALUES (SOURCE.[TABLE_CATALOG]
,SOURCE.[TABLE_SCHEMA]
,SOURCE.[TABLE_NAME]
,SOURCE.[COLUMN_NAME]
,SOURCE.[ORDINAL_POSITION]
,SOURCE.[COLUMN_DEFAULT]
,SOURCE.[IS_NULLABLE]
,SOURCE.[DATA_TYPE]
,SOURCE.[CHARACTER_MAXIMUM_LENGTH]
,SOURCE.[CHARACTER_OCTET_LENGTH]
,SOURCE.[NUMERIC_PRECISION]
,SOURCE.[NUMERIC_PRECISION_RADIX]
,SOURCE.[NUMERIC_SCALE]
,SOURCE.[DATETIME_PRECISION]
,SOURCE.[CHARACTER_SET_CATALOG]
,SOURCE.[CHARACTER_SET_SCHEMA]
,SOURCE.[CHARACTER_SET_NAME]
,SOURCE.[COLLATION_CATALOG]
,SOURCE.[COLLATION_SCHEMA]
,SOURCE.[COLLATION_NAME]
,SOURCE.[DOMAIN_CATALOG]
,SOURCE.[DOMAIN_SCHEMA]
,SOURCE.[DOMAIN_NAME]);
----- 2. Durchlauf wegen Versionierung und neuen Insert DW_ETL_Objects_detail
MERGE [DWH_Meta].[dbo].[DW_ETL_Objects_detail] AS TARGET
USING #xHelper_Objects_detail AS SOURCE
ON (
TARGET.[TABLE_CATALOG]= SOURCE.[TABLE_CATALOG]
AND TARGET.[TABLE_SCHEMA]= SOURCE.[TABLE_SCHEMA]
AND TARGET.[TABLE_NAME]= SOURCE.[TABLE_NAME]
AND TARGET.[COLUMN_NAME]= SOURCE.[COLUMN_NAME]
AND TARGET.[ORDINAL_POSITION]= SOURCE.[ORDINAL_POSITION] AND TARGET.[Date of Expiry] = '01.01.2099')
WHEN NOT MATCHED BY TARGET
THEN INSERT ([TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[COLUMN_NAME]
,[ORDINAL_POSITION]
,[COLUMN_DEFAULT]
,[IS_NULLABLE]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[DOMAIN_CATALOG]
,[DOMAIN_SCHEMA]
,[DOMAIN_NAME]) VALUES (SOURCE.[TABLE_CATALOG]
,SOURCE.[TABLE_SCHEMA]
,SOURCE.[TABLE_NAME]
,SOURCE.[COLUMN_NAME]
,SOURCE.[ORDINAL_POSITION]
,SOURCE.[COLUMN_DEFAULT]
,SOURCE.[IS_NULLABLE]
,SOURCE.[DATA_TYPE]
,SOURCE.[CHARACTER_MAXIMUM_LENGTH]
,SOURCE.[CHARACTER_OCTET_LENGTH]
,SOURCE.[NUMERIC_PRECISION]
,SOURCE.[NUMERIC_PRECISION_RADIX]
,SOURCE.[NUMERIC_SCALE]
,SOURCE.[DATETIME_PRECISION]
,SOURCE.[CHARACTER_SET_CATALOG]
,SOURCE.[CHARACTER_SET_SCHEMA]
,SOURCE.[CHARACTER_SET_NAME]
,SOURCE.[COLLATION_CATALOG]
,SOURCE.[COLLATION_SCHEMA]
,SOURCE.[COLLATION_NAME]
,SOURCE.[DOMAIN_CATALOG]
,SOURCE.[DOMAIN_SCHEMA]
,SOURCE.[DOMAIN_NAME]);
--DW_ETL_Objects
MERGE [DWH_Meta].[dbo].[DW_ETL_Objects] AS TARGET
USING #xHelper_Objects AS SOURCE
ON (
TARGET.[QuellDatabase]= SOURCE.[QuellDatabase]
AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
AND TARGET.[ObjectName]= SOURCE.[ObjectName]
AND TARGET.[Date of Expiry] = '01.01.2099')
WHEN MATCHED AND CONVERT(NVARCHAR(MAX), TARGET.[ObjectDefinition]) <> CONVERT(NVARCHAR(MAX), SOURCE.[ObjectDefinition] )
THEN UPDATE SET TARGET.[Date of Expiry] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[Date of Expiry] = '01.01.2099' THEN UPDATE SET TARGET.[Date of Expiry] = getdate()
WHEN NOT MATCHED BY TARGET
THEN INSERT ([QuellDatabase]
,[ObjectTYPE]
,[ObjectName]
,[ObjectDefinition]
) VALUES (SOURCE.[QuellDatabase]
,SOURCE.[ObjectTYPE]
,SOURCE.[ObjectName]
,SOURCE.[ObjectDefinition]
);
-- 2. Durchlauf wegen Versionierung und neuen Insert DW_ETL_Objects
MERGE [DWH_Meta].[dbo].[DW_ETL_Objects] AS TARGET
USING #xHelper_Objects AS SOURCE
ON (
TARGET.[QuellDatabase]= SOURCE.[QuellDatabase]
AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
AND TARGET.[ObjectName]= SOURCE.[ObjectName]
AND TARGET.[Date of Expiry] = '01.01.2099')
WHEN NOT MATCHED BY TARGET
THEN INSERT ([QuellDatabase]
,[ObjectTYPE]
,[ObjectName]
,[ObjectDefinition]
) VALUES (SOURCE.[QuellDatabase]
,SOURCE.[ObjectTYPE]
,SOURCE.[ObjectName]
,SOURCE.[ObjectDefinition]
);
CLOSE cUpdateStatistics
DEALLOCATE cUpdateStatistics
GO
Um diese Prozedure auszuführen dient folgender Code:
USE [DWH_Meta]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[MetaRefresh]
---da diese Prozedure ein Merge nutzt und bei diesem kein kombinierter update insert Statement funktioniert, führe ich die eigentliche Prozedure zwei mal aus :(
EXEC @return_value = [dbo].[MetaRefresh]
SELECT 'Return Value' = @return_value
GO
Hinterlasse jetzt einen Kommentar