Share via

Different Status bits of sysdatabases in SQLServer

  • Article

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

Comments