Different Status bits of sysdatabases in SQLServer
Posted by Karthick P.K on April 11, 2009
Sysdatabases.status and sysdatabases.status2 Bit of Sysdatabases in SQLServer
======================================================
<Script1>
declare @status int;
declare @status2 int;
Set @status=’65544′ –Replace your DB status here
set @status2=’1090520064′ –Replace your DB status2 here
SELECT CASE (@status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (@status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (@status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (@status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (@status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (@status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (@status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (@status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (@status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (@status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (@status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (@status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (@status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (@status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (@status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (@status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (@status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (@status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (@status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (@status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (@status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (@status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (@status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (@status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled
</Script1>
=================================================================================================================
<Script2>
SELECT substring(name, 1, 50) as dbname, cmptlevel, filename, version, CASE (status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose,
CASE (status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,
CASE (status & 8) WHEN 8 THEN 1 ELSE 0 END AS trunclogonchkpt,
CASE (status & 16) WHEN 16 THEN 1 ELSE 0 END AS tornpagedetection,
CASE (status & 32) WHEN 32 THEN 1 ELSE 0 END AS loading, CASE (status & 64) WHEN 64 THEN 1 ELSE 0 END AS prerecovery, CASE (status & 128) WHEN 128 THEN 1 ELSE 0 END AS recovering, CASE (status & 256) WHEN 256 THEN 1 ELSE 0 END AS notrecovered, CASE (status & 512) WHEN 512 THEN 1 ELSE 0 END AS offline, CASE (status & 1024) WHEN 1024 THEN 1 ELSE 0 END AS readonly, CASE (status & 2048) WHEN 2048 THEN 1 ELSE 0 END AS dbouseonly, CASE (status & 4096) WHEN 4096 THEN 1 ELSE 0 END AS singleuser, CASE (status & 32768) WHEN 32768 THEN 1 ELSE 0 END AS emergencymode,
CASE (status & 4194304) WHEN 4194304 THEN 1 ELSE 0 END AS autoshrink, CASE (status & 1073741824) WHEN 1073741824 THEN 1 ELSE 0 END AS cleanlyshutdown,
CASE (status2 & 16384) WHEN 16384 THEN 1 ELSE 0 END AS ansinulldefault, CASE (status2 & 65536) WHEN 65536 THEN 1 ELSE 0 END AS concatnullyieldsnull,
CASE (status2 & 131072) WHEN 131072 THEN 1 ELSE 0 END AS recursivetriggers, CASE (status2 & 1048576) WHEN 1048576 THEN 1 ELSE 0 END AS defaulttolocalcursor,
CASE (status2 & 8388608) WHEN 8388608 THEN 1 ELSE 0 END AS quotedidentifier, CASE (status2 & 33554432) WHEN 33554432 THEN 1 ELSE 0 END AS cursorcloseoncommit,
CASE (status2 & 67108864) WHEN 67108864 THEN 1 ELSE 0 END AS ansinulls, CASE (status2 & 268435456) WHEN 268435456 THEN 1 ELSE 0 END AS ansiwarnings,
CASE (status2 & 536870912) WHEN 536870912 THEN 1 ELSE 0 END AS fulltextenabled FROM sysdatabases where name = ‘master’
go
SELECT fileid, name, filename, size, maxsize, growth, groupid
FROM sysaltfiles WHERE dbid IN ( SELECT dbid FROM sysdatabases where name = ‘ Replace DB Name’ )
go
SELECT fileid, name, filename, size, maxsize, growth, groupid FROM sysfiles
go
SELECT groupid, groupname, CASE (status & 0x8) WHEN 0x8 THEN 1 ELSE 0 END AS readonly,
CASE (status & 0x10) WHEN 0x10 THEN 1 ELSE 0 END AS isdefault FROM sysfilegroups WHERE groupid IN ( SELECT groupid FROM sysaltfiles a, sysdatabases b WHERE a.dbid = b.dbid AND b.name = ‘Replace DB Name’ )
</Script2>
Regards
Karthick P.K
MSSQLWIKI 
"how together said
Very descriptive blog, I liked that bit.
Will there be a part 2?
is it ok to text your ex said
We are a group of volunteers and starting a brand new scheme in our
community. Your site provided us with helpful info to work on.
You have performed an impressive job and our
whole group can be thankful to you.
Www.Youtube.com said
Great post. I was checking continuously this blog and I am inspired!
Extremely helpful info particularly the remaining section 🙂 I maintain such information much.
I used to be seeking this certain info for a long time. Thanks and best of luck.
品質 フラットシューズ 正規品 said
I am not sure where you are getting your information, but great
topic. I needs to spend some time learning much more or
understanding more. Thanks for great info I was looking for this info for
my mission.
receta de arroces said
No conocía esta web hasta hoy, me lo guardo en marcadores!
¿Hace cuanto que lo creaste? Vendré más con frecuencia, tiene un diseño muy agradable.
|
Leerte es inspirador, dan ganas de ponerse a escribir .
|
Ey! Puedo usar una imagen de este blog para Puedo coger tus
fotografías para mi mi web? No veo en la red imágenes adecuadas
para mi web y las tuyas están geniales. Muchísimas gracias!
|
Este blog se usa el mismo diseño que tenía instalado en el mío!
¿Puedes por favor contarme cómo se llamaba? Lo olvidé y no soy capaz de recuperarlo…
Muchas gracias.
|
Muchas gracias por compartir artículos como este.
|
Me gusta el artículo. Me guardo la RSS y así
no dejarme nada en el futuro.
|
Muy curiosa la información. Ánimo con el resto de artículos.
|
Me gusta mucho este blog. Post como este son los que uno espera encontrar.
Philipp said
Hello, this weekend is fastidious for me, because this occasion i am reading this wonderful educational article here at my
residence.
Bright Start Academy said
Article writing is also a excitement, if you be acquainted
with then you can write if not it iis complicated to write.