I recently stumbled across a great system view, sys.dm_db_persisted_sku_features, which identifies any enterprise features in use in the current database, which would prevent you from moving this database to a Standard Edition instance. Unfortunately, it appears in SQL 2008, and I wanted to run this check on a SQL 2005 system.
There are a number of server-level features of SQL 2005 that require Enterprise Edition, but only two database-level features – partitioning and the VarDecimal storage format. Both are easy to check for, so I put together this quick query to do it:
select * from (-- VarDecimal Storage Format select case when max(objectproperty(object_id, N'TableHasVarDecimalStorageFormat')) = 0 then '' else 'VarDecimal Storage' end as feature_name from sys.objects UNION ALL -- Partitioning select case when max(partition_number) > 1 then 'Partitioning' else '' end from sys.partitions ) t where feature_name <> ''On a SQL 2005 server, this query will serve the same purpose that sys.dm_db_persisted_sku_features does on SQL 2008/2012 servers.
This is extremely useful for us still using archaic systems such as MSSQL 2005 – Many thanks for your contribution.
I was wondering why “sys.dm_db_persisted_sku_features” wasn’t working 😀
I tried execute this query on SQl Server 2005 and got error message (see below)
select * from
(– VarDecimal Storage Format
select case
when max(objectproperty(object_id, N’TableHasVarDecimalStorageFormat’)) = 0
then ”
else ‘VarDecimal Storage’
end as feature_name
from sys.objects
UNION ALL
— Partitioning
select case
when max(partition_number) > 1
then ‘Partitioning’
else ”
end
from sys.partitions
) t
where feature_name <> ”
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘;’.
When I click on message it show line
when max(partition_number) > 1
It looks like it’s converting the “>” to “>” when it gets displayed for you to copy, so you’ll need to change that back to “>”. It’s also doing it in the last line where it converts “<>” to “<>” so replace that one too and it will work as expected. Not sure why it’s doing that, but I’ll try to get the code sample updated so it doesn’t cause issues for anybody else – thanks for letting me know!