SQLDBA - LEGACY_CARDINALITY_ESTIMATION is enabled ON or OFF

 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: