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