I have SQL Server that was installed as enterprise edition, we have never utilized the enterprise-only features on this particular server and want to downgrade to Standard edition so that we can make use of this enterprise license in some other server.
There’s an easy way to tell whether the database contains enterprise-only features. In SQL Server 2008 onwards a new DMV sys.dm_db_persisted_sku_features has been added that will report you which enterprise only features are present in a database. Let’s check it out.
You need to run below query on every database to check for enterprise-only features.
SELECT * FROM sys.dm_db_persisted_sku_features
The following query will report all enterprise-only features from all databases.
Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255)) INSERT INTO @EntFeatures exec sp_msforeachdb 'select "?" AS DatabaseNames,feature_name from [?].sys.dm_db_persisted_sku_features' SELECT * FROM @EntFeatures
The following query makes use of cursor to achieve the same result as in method 2.
Declare @dbid Int Declare @STR Varchar(100) Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255)) Declare rs scroll cursor for Select database_id From sys.databases open rs fetch first from rs into @dbid while @@fetch_status= 0 Begin Select @STR = 'use ' + db_name (@dbid) + CHAR(13) + ' SELECT db_name(),feature_name FROM sys.dm_db_persisted_sku_features' Insert Into @EntFeatures EXEC ( @STR ) fetch next from rs into @dbid End Close rs Deallocate rs Select * from @EntFeatures
sys.dm_db_persisted_sku_features DMV only reports below four features.
- Data compression
- Transparent data encryption
- Change data capture
As per MSDN Library even Database Snapshot & Online Indexing are enterprise-only features , if you have enabled these features in your database it will not be reported by this DMV.
To make sure I created a database snapshot and ran this DMV but it did not report database snapshot as enterprise-only feature same is with online indexing which I enabled for one of my maintenance plans but DMV again missed out this as well.