[SQL Server] In-Memory OLTP : Vérifier la configuration des bases et des tables

  • Imprimer

DROP TABLE IF EXISTS #verif_config_database
DROP TABLE IF EXISTS #verif_create_table

CREATE TABLE #verif_config_database
(
num_lig INT IDENTITY (1,1),
nom_base VARCHAR(65) NULL,
is_memory_optimized_elevate_to_snapshot_on INT NULL,
file_group_InMemory VARCHAR(65) NULL,
name_file VARCHAR(150) NULL,
[type_desc] VARCHAR(40) NULL,
[physical_name] VARCHAR(MAX) NULL,
[FileGroup_Name] VARCHAR(65) NULL
)

CREATE TABLE #verif_create_table
(
num_lig INT IDENTITY (1,1),
nom_base VARCHAR(65) NULL,
nom_table VARCHAR(65) NULL,
[type] CHAR(1) NULL
)

INSERT INTO #verif_config_database(nom_base,is_memory_optimized_elevate_to_snapshot_on,file_group_InMemory
,[name_file],[type_desc],physical_name,[FileGroup_Name])
execute sp_MSforeachdb'
USE [?];
if DB_NAME() LIKE (''MaBase%'')
SELECT DB_NAME() as nom_base,is_memory_optimized_elevate_to_snapshot_on
,(SELECT [name] FROM sys.filegroups WHERE [name]=''FG_MEMORY_OPTIMIZED'' AND [type]=''FX'') AS file_group_InMemory
,T1.name as [name_file],T1.[type_desc],T1.physical_name,T3.[name] AS [FileGroup_Name] FROM sys.database_files T1
JOIN sys.databases T2 ON (db_id() = T2.database_id)
AND T1.type = 2
JOIN sys.filegroups T3 ON T3.data_space_id=T1.data_space_id
WHERE T3.type=''FX'''

INSERT INTO #verif_create_table(nom_base,nom_table,[type])
execute sp_MSforeachdb'
USE [?];
if DB_NAME() LIKE (''MaBase%'')
SELECT DB_NAME() as nom_base, [name] as nom_table,[type] FROM sys.objects
WHERE object_id in ( OBJECT_ID(N''[dbo].[SA_XTGT_SOURCE_ECO_MOT]'')
, OBJECT_ID(N''[dbo].[MaTable_1]'') , OBJECT_ID(N''[dbo].[MaTable_2]'') , OBJECT_ID(N''[dbo].MaTable_3]'')
, OBJECT_ID(N''[dbo].[MaTable_4]'') ) AND type in (N''U'')
'
SELECT * FROM #verif_config_database
SELECT * FROM #verif_create_table

DROP TABLE IF EXISTS #verif_config_database
DROP TABLE IF EXISTS #verif_create_table