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,
	[GueltigVon] [datetime] NOT NULL,
	[GueltigBis] [datetime] NOT NULL,
 CONSTRAINT [PK_DW_ETL_Objects_detail] PRIMARY KEY CLUSTERED 
(
	[TABLE_CATALOG] ASC,
	[TABLE_SCHEMA] ASC,
	[TABLE_NAME] ASC,
	[COLUMN_NAME] ASC,
	[GueltigVon] 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 [GueltigVon]
GO

ALTER TABLE [dbo].[DW_ETL_Objects_detail] ADD  DEFAULT ('01/01/2099') FOR [GueltigBis]
GO



IF OBJECT_ID('dbo.[DW_ETL_Objects]', 'U') IS NOT NULL 
 DROP TABLE dbo.[DW_ETL_Objects]; 


CREATE TABLE [dbo].[DW_ETL_Objects](
	[ObjectDatabase] [varchar](255) NOT NULL,
	[ObjectSCHEMA] [nvarchar](50) NOT NULL,
	[ObjectTYPE] [varchar](50) NOT NULL,
	[ObjectName] [sysname] NOT NULL,
	[ObjectDefinition] [ntext] NULL,
	[GueltigVon] [datetime] NOT NULL,
	[GueltigBis] [datetime] NOT NULL,
 CONSTRAINT [PK_DW_ETL_Objects] PRIMARY KEY CLUSTERED 
(
	[ObjectDatabase] ASC,
	[ObjectSCHEMA] ASC,
	[ObjectTYPE] ASC,
	[ObjectName] ASC,
	[GueltigVon] 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] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[DW_ETL_Objects] ADD  DEFAULT (getdate()) FOR [GueltigVon]
GO

ALTER TABLE [dbo].[DW_ETL_Objects] ADD  DEFAULT ('01/01/2099') FOR [GueltigBis]
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   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
    (  
	[ObjectDatabase] [varchar](255) NOT NULL,
	[ObjectSCHEMA] [nvarchar](50) 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  ([ObjectDatabase],[ObjectSCHEMA], [ObjectTYPE],[ObjectName],[ObjectDefinition]) 
SELECT '''+ QUOTENAME(@Databases) +''' AS ObjectDatabase,
s.name as [ObjectSCHEMA], 
o.type_desc AS ObjectTYPE, o.name 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  
JOIN '+ QUOTENAME(@Databases) +'.sys.schemas as s with (nolock) ON o.schema_id = s.schema_id
WHERE o.type IN (''P '', ''V'')
UNION ALL
 SELECT '''+ QUOTENAME(@Databases) +''' AS ObjectDatabase,
 TABLE_SCHEMA as [ObjectSCHEMA],
  ''Table'' AS ObjectTYPE ,TABLE_NAME as ObjectName,
 '''' as ObjectDefinition
FROM '+ QUOTENAME(@Databases) +'.INFORMATION_SCHEMA.TABLES with (nolock) 
where TABLE_TYPE = ''BASE TABLE'' ORDER BY [ObjectDatabase],[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.[GueltigBis] = '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.[GueltigBis] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[GueltigBis] = '01.01.2099' THEN UPDATE SET TARGET.[GueltigBis] = 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.[GueltigBis] = '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.[ObjectDatabase]= SOURCE.[ObjectDatabase]
  AND TARGET.[ObjectSCHEMA]= SOURCE.[ObjectSCHEMA]
 AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
 AND TARGET.[ObjectName]= SOURCE.[ObjectName]
  AND TARGET.[GueltigBis] = '01.01.2099') 
WHEN MATCHED AND  CONVERT(NVARCHAR(MAX), TARGET.[ObjectDefinition]) <>  CONVERT(NVARCHAR(MAX), SOURCE.[ObjectDefinition] )
THEN UPDATE SET TARGET.[GueltigBis] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[GueltigBis] = '01.01.2099' THEN UPDATE SET TARGET.[GueltigBis] = getdate() 
WHEN NOT MATCHED BY TARGET 
THEN INSERT ([ObjectDatabase]
	  ,[ObjectSCHEMA]
      ,[ObjectTYPE]
      ,[ObjectName]
      ,[ObjectDefinition]
      )  VALUES (SOURCE.[ObjectDatabase]
	  	  ,SOURCE.[ObjectSCHEMA]
      ,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.[ObjectDatabase]= SOURCE.[ObjectDatabase]
 AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
   AND TARGET.[ObjectSCHEMA]= SOURCE.[ObjectSCHEMA]
 AND TARGET.[ObjectName]= SOURCE.[ObjectName]
  AND TARGET.[GueltigBis] = '01.01.2099') 
WHEN NOT MATCHED BY TARGET 
THEN INSERT ([ObjectDatabase]
	  	  ,[ObjectSCHEMA]
      ,[ObjectTYPE]
      ,[ObjectName]

      ,[ObjectDefinition]
      )  VALUES (SOURCE.[ObjectDatabase]
	  	  	  ,SOURCE.[ObjectSCHEMA]
      ,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


CREATE TABLE [dbo].[DWH_SpaceInfo](
	[DBName] [nvarchar](128) NOT NULL,
	[Schema] [nvarchar](128) NOT NULL,
	[Table] [nvarchar](128) NOT NULL,
	[RowCount] [int] NULL,
	[SizeInMB] [int] NULL,
	[CreateDate] [datetime] NOT NULL,
	[DateID] [int] NULL
) ON [PRIMARY]
GO

USE [DWH_META]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER Procedure [dbo].[MetaSpaceRefresh]

AS 

Delete from  [dbo].[DWH_SpaceInfo] WHERE [DateID] = CONVERT(int,Convert(varchar(10),getDate(),112));

Declare @src NVARCHAR(MAX), @sql NVARCHAR(MAX); 

Select @sql = N'', @src = N' UNION ALL 
Select ''$d'' AS ''DBName'', 
	s.name COLLATE SQL_LAtin1_General_CP1_CI_AI AS ''Schema'', 
	t.name COLLATE SQL_LAtin1_General_CP1_CI_AI AS ''Table'', 
	ind.rows AS [RowCount],
	(ind.used* 8) / 1024 AS SizeInMB, 
	t.Create_Date,
	Convert(int,convert(varchar(10),getdate(),112)) AS DateID
From [$d].sys.schemas as S 
	inner Join [$d].sys.tables as t on s.[schema_id] = t.[schema_id]
	inner join [$d].sys.sysindexes as ind on t.[object_id] = ind.[id]
	where ind.indid  < 2';

	Select @sql = @sql + REPLACE(@src, '$d', name)
	 from sys.databases
	 where database_id>4
	 AND [state] =0
	 AND name not in ('master', 'tempdb',  'model')
	 AND HAS_DBACCESS(Name) =1;

	 Set @sql= STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));

	 EXEC('Insert into [DWH_META].[dbo].[DWH_SpaceInfo]' + @sql);

GO

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*