Finding databases which use 'Enterprise' features in SQL Server

30 Jun 2015

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 
BEGIN 
   INSERT INTO ##enterprise_features 
    SELECT dbname=DB_NAME(),feature_name,feature_id 
    FROM sys.dm_db_persisted_sku_features 
END '
SELECT *
FROM   ##enterprise_features 

Add new comment

The content of this field is kept private and will not be shown publicly.

Restricted HTML

  • Allowed HTML tags: <a href hreflang target> <em> <strong> <cite> <blockquote cite> <pre> <ul type> <ol start type> <li> <dl> <dt> <dd> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

Get a quote in 24 hours

Wether a huge commerce system, or a small business website, we will quote the project within 24h of you pressing the following button: Get quote