March 2, 2007 at 9:32 am
To Yelena & jmcgarvey: Thanks ... that's exactly what I was looking for ... so much for coming to computers through logic, and not math. Much appreciated!
Joseph
Goal: To select all db's on a server, except for read-only.
My approach has been to read 'status' field from sysdatabases (2000) or sys.sysdatabases. SqlBOL indicates a 'status' value of 1024 is read-only. I have known r-o db's with status = 1052, 1073742848 as examples.
a) are these values 'and'ed, 'or'ed?; b) any idea on an approach to 'extract' the read-only value (1024) from these statuses; c) a better approach to identify read-only db's?
Joseph
March 2, 2007 at 10:42 am
If you enter each of 1024, 1054 and 1073742848 in the Calculator, set View->Scientific and click Bin button, then you will see that 1024 bit correspond to 1 (one) in the eleventh position from the right. Each of these numbers have 1 in this position. So you have to write a query that converts the status to binary and select the ones with the 1 in eleventh position.
Also, you can use sp_dboption with the option "read only" to see if it is true.
Regards,Yelena Varsha
March 2, 2007 at 10:51 am
This script will help you find the readonly databases on the server. The real meat of the script is highlighted. If a db is readonly you will receive a 1 else 0. The function of the rest of the script will simply build a temp table, insert the information for every db and provide a structured output. No big deal. You can find additional information on the DATABASEROPERTY and DATABASEROPERTYEX key words in BOL. There are a number of functions here.
if exists (select 1 from tempdb.dbo.sysobjects where name like '%dbmode%' and type = 'u')
begin
drop table ##dbmode
end
create table ##dbmode (
DBName varchar(60),
Mode sql_variant)
set nocount on
exec master.dbo.sp_msforeachdb
"USE [?]
insert ##dbmode
SELECT db_name(),DATABASEPROPERTY('?', 'IsReadOnly')
"
select * From ##dbmode
order by dbname
Good Luck
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply