Identify system databases

[Former Member]

Hi There,

I would like to identify if a database is a system database or  a tempdb (durability no_recovery)

At this moment I know the following methodes, but they are not 'waterproof' !

- use name of the databases (master, model, tempdb, sybsystemprocs, sybsecurity, dbccdb, sybmgmtdb)

- select name, id from master..sysdatabases where durability = 6 and dbid < 4 and dbid > 31500 (or something like this)

Are there other way's to identify them?

Regards,

Harry

[Former Member]

Thanks for the list!!!! I'll add them.

[Former Member]

I'd add sybsyntax to the list of "system" databases...

Unfortunately the "system" databases are not marked (yet?) in ASE.  You cannot identify one as system database unless you know it by name.  Pretty annoying as it would take zero effort to add this functionality. Some sites introduce range conventions to "create database" usage (with dbid = {range for udbs}).  Relying on durability flag too may mislead as you may create user database with durability = no_recovery as well.

I'm afraid you have little choice but to maintain the list of names (put it into your own catalogue table). 

Maybe ASE 20 will have it...  unless ASE will by then merge into HANA as scratch DB...

[Former Member]

Hi Andrew,

thanks for adding another one to the list. The range was something we already thought off, but as you say. Not Ideal as well as the durability.

I would have been nice to have this without a hard coded list 😞

Thanks for the input ! 🙂

[Former Member]

Hi Andrew,

thanks for adding another one to the list. The range was something we already thought off, but as you say. Not Ideal as well as the durability.

I would have been nice to have this without a hard coded list 😞

Thanks for the input ! 🙂