Karthick PK 's Blog

Karthick PK's technical blog covering topics such as SQLServer troubleshooting, technologies and security.

Different Status bits of sysdatabases in SQLServer

Different Status bits of sysdatabases in SQLServer

Rate This
  • Comments 5

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
Page 1 of 1 (5 items)
Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post