Post reply

How to filter out read-only databases

  • November 29, 2006 at 8:13 pm

    #175042

    Hi,

    I am trying to select all databases on the server except the read-only ones.  Read only status is equal to 1024, so all databases with read-only option have to have status equal or greater to 1024.  I have these values for status on the server:

    6292496
    4194320
    1048
    4194316
    4194316
    1036
    2098192
    2098192
    4194316
    1073741840
    2098192

    but only the following are read only databases:

    6292496
    1048
    1036
    2098192
    2098192
    2098192

    How do I make the difference?

    I understand, that one could run sp_dboption and use the output to find out, but I wish I could do it with just one query.

    Thanks.

     

  • November 29, 2006 at 8:37 pm

    #674838

    Hi,

    You may try like this

    SELECT  [Name] FROM SysDataBases

    WHERE DatabasePropertyEx([Name],'Updateability') = 'READ_ONLY'

    Ram

  • November 29, 2006 at 8:39 pm

    #674839

    Oops

    Try this

    SELECT  [Name] FROM SysDataBases

    WHERE DatabasePropertyEx([Name],'Updateability') <> 'READ_ONLY'

  • November 30, 2006 at 3:41 am

    #674874 

    To check bit flags you have to AND the column with the value of the bit you are trying to test

eg:
SELECT name,dbid,status FROM master..sysdatabases
where (status & 1024)=1024

    Nigel
    How to post questions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply