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.