April 24, 2017 at 5:34 pm
Currently we have a query that re-indexes all databases but fails when it gets to a "read-only" database since it can't "update" those indexes. We would like to exclude those databases that are "read-only". We also need to exclude those databases that are "loading" and are "offline". So my question is, why can't I find the same values in sys.databases that I can find in sysdatabases?
Below is the code we have, how can I add in "exclude those that are read only" in the code as well? One thing. I do see a "1024" for the status of "read only" in lots of threads about this. But the status we are seeing in the master..sysdatabases = 1073808384.
Below is the code we have? Can the "Loading" and "OffLine" be written using sys.databases?
SELECT A.Name, status
FROM master..sysdatabases A with(nolock)
WHERE A.name not in ('tempdb', 'master', 'msdb', 'pubs', 'model')
and A.status & 32 = 0
and A.status & 512 = 0
Thank you gurus!!!
April 24, 2017 at 6:22 pm
There is actually more information in sys.databases than there is/was in sysdatabases and it's a bit more straightforward - no status bits to deal with. You are looking for the column named state. Restoring and Offline are values for state in that view. And there is another column called is_read_only.
You can find the documentation here:
sys.databases
Sue
April 25, 2017 at 1:25 am
Edward Shaw - Monday, April 24, 2017 5:34 PMCurrently we have a query that re-indexes all databases but fails when it gets to a "read-only" database since it can't "update" those indexes. We would like to exclude those databases that are "read-only". We also need to exclude those databases that are "loading" and are "offline". So my question is, why can't I find the same values in sys.databases that I can find in sysdatabases?Below is the code we have, how can I add in "exclude those that are read only" in the code as well? One thing. I do see a "1024" for the status of "read only" in lots of threads about this. But the status we are seeing in the master..sysdatabases = 1073808384.
Below is the code we have? Can the "Loading" and "OffLine" be written using sys.databases?
SELECT A.Name, status
FROM master..sysdatabases A with(nolock)
WHERE A.name not in ('tempdb', 'master', 'msdb', 'pubs', 'model')
and A.status & 32 = 0
and A.status & 512 = 0Thank you gurus!!!
Don't use sys.sysdatabases, it is there for backward compatibility to SQL 2000.
😎
This should work for you:SELECT
SDB.name
,SDB.state_desc
FROM sys.databases SDB
WHERE SDB.state = 0;
April 25, 2017 at 10:50 am
Thank you. That worked.
April 28, 2017 at 3:00 am
As already answered, sysdatabases are for backward compatibility, but instead of creating your own script for re-indexing, you should consider using the Ola Hallengren maintenance solution. Read more at ola.hallengren.com
It's used world wide by many companies and handles all editions of SQL server, even with AlwaysOn availability activated for databases.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply