In situations where SQL Server database servers are being migrated to new instances and the source instance is running Enteprise Edition, the question often arises: do we need to use Enterprise edition? Standard Edition is cheaper!
While this isn't a question which can be answered by functionality alone, (i.e. there non functional requirements like performance & high availability which may require Enterprise) there is a quick way of finding out which databases are using enterprise functionality using the sys.dm_db_persisted_sku_features DMV.
Here is a T-SQL query which queries all databases on an instance and will return databases which are utilising Enterprise features:
- IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
- DROP TABLE ##enterprise_features
- CREATE TABLE ##enterprise_features
- dbname SYSNAME,
- feature_name VARCHAR(100),
- feature_id INT
- EXEC sp_msforeachdb
- N' USE [?]
- IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0
- INSERT INTO ##enterprise_features
- SELECT dbname=DB_NAME(),feature_name,feature_id
- FROM sys.dm_db_persisted_sku_features
- END '
- SELECT *
- FROM ##enterprise_features