How to get the SPS number from a SAP HANA database?

Question

I'm currently working on a script which writes metadata to system tables of an SAP HANA database. For one example, I need to differentiate between a HANA database with an Support Package Stack of version 05 or higher. Is there any system table I can query to get the used SPS version ?

Thanks for your help! André

Answer 1

System view M_DATABASE_HISTORY contains version history for the database, including install time. If you look at its content

select * from m_database_history order by install_time desc

you'll get something like:

INSTALL_TIME;VERSION

04.03.2020, 16:17:31.0;2.00.046.00.1581325702

13.05.2020, 14:02:00.0;2.00.047.00.1586595995

02.07.2020, 19:40:20.0;2.00.048.00.1591276203

To get the currently installed revision and extract the SPS from it, you could so something like:

select substring (version, 7, 1) from m_database_history where install_time = (select max(install_time) from m_database_history)

I'm not sure what you mean by "writes metadata to system tables of an SAP HANA database". I hope you're trying to write to self-created tables in the SYSTEM schema and not to write into HANA-builtin system tables as this might become problematic.