Hi ,
Below query will help us for find out weather the data base having LEGACY_CARDINALITY_ESTIMATION on or of.
IF OBJECT_ID('tempdb..#LegacyCE') IS NOT NULL
DROP TABLE #LegacyCE;
CREATE TABLE #LegacyCE
(
DatabaseName SYSNAME,
ConfigName NVARCHAR(100),
Value INT
);
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += '
USE [' + name + '];
INSERT INTO #LegacyCE (DatabaseName, ConfigName, Value)
SELECT
DB_NAME(),
name,
CONVERT(INT, CONVERT(VARCHAR(10), value))
FROM sys.database_scoped_configurations
WHERE name = ''LEGACY_CARDINALITY_ESTIMATION'';
'
FROM sys.databases
WHERE state_desc = 'ONLINE';
EXEC sp_executesql @sql;
SELECT *
FROM #LegacyCE
ORDER BY DatabaseName;
out put:
