Find all read-only Databases using Stored Procedure

26 Jan 2006 7:54 PM

MKruer

I am running a Stored Procedure that goes through all the databases and
reindexs them. However when it reaches a database that is "read
only" the program quits with an error. If there an easy way to
determine if the database is Read only and skip it if it is?

Using SQL 2000 / 2005
Thanks
-Matt-

DECLARE @ExecSQLcmd varchar(1024)
DECLARE @DBNum_to_Name int

SELECT @DBNum_to_Name =min(dbid)
FROM   master.dbo.sysdatabases
WHERE dbid > 4

WHILE @DBNum_to_Name is not null
BEGIN
    SELECT @ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
100'
    FROM master.dbo.sysdatabases
    WHERE dbid =@DBNum_to_Name
    EXEC (@ExecSQLcmd)
    SELECT @DBNum_to_Name =min(dbid)
    FROM master.dbo.sysdatabases
    WHERE dbid > @DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
DBs here.
END

26 Jan 2006 8:00 PM

Aaron Bertrand [SQL Server MVP]

AND DATABASEPROPERTY(name, 'IsReadOnly') = 0

<MKr***@gmail.com> wrote in message
Show quote

news:1138305254.208071.14400@g14g2000cwa.googlegroups.com...
>I am running a Stored Procedure that goes through all the databases and
> reindexs them. However when it reaches a database that is "read
> only" the program quits with an error. If there an easy way to
> determine if the database is Read only and skip it if it is?
>
> Using SQL 2000 / 2005
> Thanks
> -Matt-
>
>
> DECLARE @ExecSQLcmd varchar(1024)
> DECLARE @DBNum_to_Name int
>
> SELECT @DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
>
> WHILE @DBNum_to_Name is not null
> BEGIN
> SELECT @ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
> 100'
> FROM master.dbo.sysdatabases
> WHERE dbid =@DBNum_to_Name
> EXEC (@ExecSQLcmd)
> SELECT @DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
> DBs here.
> END
>