Für die Datenbank DWH_Meta die erforderlichen Tabellen anlegen


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: 30.04.2021 22:29:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create 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')


DROP table IF EXISTS [DWH_Meta].[dbo].xHelper_Objects;
DROP table IF EXISTS [DWH_Meta].[dbo].xHelper_Objects_detail;  
CREATE TABLE [DWH_Meta].[dbo].xHelper_Objects
  
    (  
	[QuellDatabase] [varchar](255) NOT NULL,
	[ObjectTYPE] [varchar](5) NOT NULL,
	[ObjectName] [sysname] NOT NULL,
	[ObjectDefinition] [ntext] NULL
    );  
CREATE TABLE [DWH_Meta].[dbo].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 [DWH_Meta].[dbo].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  [DWH_Meta].[dbo].[xHelper_Objects]  ([QuellDatabase],[ObjectTYPE],[ObjectName],[ObjectDefinition]) 
SELECT '''+ QUOTENAME(@Databases) +''' AS QuellDatabase,
 ''View'' AS ObjectTYPE,  TABLE_NAME as ObjectName,
 cast(VIEW_DEFINITION as ntext) as ObjectDefinition
FROM '+ QUOTENAME(@Databases) +'.INFORMATION_SCHEMA.Views  with (nolock) 
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 

 MERGE [DWH_Meta].[dbo].[DW_ETL_Objects_detail] AS TARGET
USING [DWH_Meta].[dbo].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 records are matched, update the records if there is any change
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 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]);



	  MERGE [DWH_Meta].[dbo].[DW_ETL_Objects] AS TARGET
USING [DWH_Meta].[dbo].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 records are matched, update the records if there is any change
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 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]
    );


 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

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*