Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+?
You could create a utility database on each server with a table(s) to hold metadata about that server (environment, purpose, build date etc). As each utility database is tied to a server/instance, it won't get refreshed along with any databases that get refreshed so you don't have to worry about it being inaccurate if a refresh fails.
Include the deployment of this DB in your standard build process and then you can simply reference this DB in your query to determine environment. If you keep the same name throughout your SQL estate, then your queries don't need to change between servers either.