Finding databases which use 'Enterprise' features in SQL Server

30 Juni 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:

  1. IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
  2. DROP TABLE ##enterprise_features
  3.  
  4. CREATE TABLE ##enterprise_features
  5. (
  6. dbname SYSNAME,
  7. feature_name VARCHAR(100),
  8. feature_id INT
  9. )
  10.  
  11. EXEC sp_msforeachdb
  12. N' USE [?]
  13. IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0
  14. BEGIN
  15. INSERT INTO ##enterprise_features
  16. SELECT dbname=DB_NAME(),feature_name,feature_id
  17. FROM sys.dm_db_persisted_sku_features
  18. END '
  19. SELECT *
  20. FROM ##enterprise_features

Neuen Kommentar hinzufügen

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.

Restricted HTML

  • Erlaubte 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>
  • Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Website- und E-Mail-Adressen werden automatisch in Links umgewandelt.

Angebot innerhalb von 24 Stunden

Ob ein großes kommerzielles System, oder eine kleine Business Seite, wir schicken ein Angebot ab innerhalb von 24 Stunden nachdem Sie diese Taste drücken: Angebot anfordern