How to find out database status is in read only or not?

• me_balaji

  SSC Eights!

  Points: 923

  More actions

  April 3, 2007 at 2:48 pm

  #95177

  What is the best way to find out whether database is readonly state or read write state? My requirement is to from a central sql box connect to all linked servers and get a list of databases that are in read only state.

  Is there anyway to know this without using sp_dboption on each database? Like  from the master database ?  Sysdatabases table status column have the bit value combined for the settings. Is there any other possibility to acheive this.

   

  Thanks...

   

   

   

   

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  April 3, 2007 at 3:15 pm

  #698409

  This should do it.

  select
   ReadOnly = databaseproperty(a.name ,'IsReadOnly'),
   a.name
  from
   master.dbo.sysdatabases a
  order by
   a.name