November 29, 2006 at 8:13 pm
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
Hi,
You may try like this
SELECT [Name] FROM SysDataBases
WHERE DatabasePropertyEx([Name],'Updateability') = 'READ_ONLY'
Ram
November 29, 2006 at 8:39 pm
Oops
Try this
SELECT [Name] FROM SysDataBases
WHERE DatabasePropertyEx([Name],'Updateability') <> 'READ_ONLY'
November 30, 2006 at 3:41 am
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