Database Chronicles

SQL Server 2005 does not allow for direct user access to the underlying database and catalog system tables as was the case in previous versions of SQL Server, where you could issue the statement:

EXEC master..sp_configure 'Allow Updates', 1
RECONFIGURE WITH OVERRIDE

These tables can now only be accessed via Direct Administrator Connection (or DAC), these tables are also exposed to DBA’s using HyperBac’s OLEDB Object Recovery Provider - Backup Explorer along with user base tables, the database catalog contained in the backup device(s) can be used to reconstruct DDL for stored procedures, views, UDFs and other objects.

A Direct Administrator Connection (DAC) can be obtained by using a connection string a SQL Server Management Studio Query Editor session as follows:

ADMIN:ServerName\InstanceName

A DAC can also be obtained using sqlcmd using the -A switch. Only members of the SQL Server sysadmin role can connect using the DAC.

NOTE: A Direct Administrator Connection should be used with caution, and all attempts should be made not to modify meta data in the SQL Server 2005 database or system catalog unless absolutely necessary

To obtain read only access to these catalog base tables from a backup device, create a Linked Server using the HyperBac Backup Explorer OLEDB Provider to a SQL Server 2005 Database Backup, the tables can now be accessed via SELECT statements to the provider

A list of these entities is below, with descriptive information following:

	syschildinsts (master database only)		sysallocunits
	sysdbreg (master database only)		sysasymkeys
	sysendpts (master database only)		sysbinobjs
	syslnklgns (master database only)		sysbinsubobjs
	syslogshippers (master database only)		syscerts
	sysrmtlgns (master database only)		sysclsobjs
	sysusermsgs (master database only)		syscolpars
	syswebmethods (master database only)		sysconvgroup
	sysxlgns (master database only)		sysdbfiles
	sysxsrvs (master database only)		sysdercv
	sysdesend		sysfiles1
	sysftinds		sysguidrefs
	syshobtcolumns		syshobts
	sysidxstats		sysiscols
	sysmultiobjrefs		sysnsobjs
	sysobjkeycrypts		sysobjvalues
	sysowners		sysprivs
	sysqnames		sysremsvcbinds
	sysrowsetcolumns		sysrowsetrefs
	sysrowsets		sysrts
	sysscalartypes		sysschobjs
	sysserefs		syssingleobjrefs
	syssqlguides		systypedsubobjs
	sysxmitqueue		sysxmlcomponent
	sysxmlfacet		sysxmlplacement
	sysxprops

syschildinsts (master database only)

Column Name	Data Type	Is Nullable	Description
lsid	varbinary (85)	YES
iname	nvarchar (128)	YES
ipipename	nvarchar (260)	YES
pid	int	YES
status	int	YES
crdate	datetime	YES
modate	datetime	YES
sysdbpath	nvarchar (260)	YES

sysdbreg (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
sid	varbinary (85)	YES
status	int	YES
restat	int	YES
category	int	YES
crdate	datetime	YES
modified	datetime	YES
svcbrkrguid	uniqueidentifier	YES
scope	int	YES

sysendpts (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
protocol	tinyint	YES
type	tinyint	YES
bstat	smallint	YES
affinity	bigint	YES
pstat	smallint	YES
tstat	smallint	YES
typeint	int	YES
port1	int	YES
port2	int	YES
site	nvarchar (128)	YES
dfltns	nvarchar (384)	YES
wsdlproc	nvarchar (776)	YES
dfltdb	nvarchar (128)	YES
authrealm	nvarchar (128)	YES
dfltdm	nvarchar (128)	YES
maxconn	int	YES
encalg	tinyint	YES
authtype	tinyint	YES

syslnklgns (master database only)

Column Name	Data Type	Is Nullable	Description
srvid	int	YES
lgnid	int	YES
name	nvarchar (128)	YES
status	int	YES
modate	datetime	YES
pwdhash	varbinary (320)	YES

syslogshippers (master database only)

Column Name	Data Type	Is Nullable	Description
name	nvarchar (128)	YES
psrv	nvarchar (128)	YES
ssrv	nvarchar (128)	YES
status	tinyint	YES
rolesequence	int	YES
safety	tinyint	YES
safetysequence	int	YES
logshippingid	uniqueidentifier	YES
familyid	uniqueidentifier	YES
statussequence	int	YES

sysrmtlgns (master database only)

Column Name	Data Type	Is Nullable	Description
srvid	int	YES
name	nvarchar (128)	YES
lgnid	int	YES
status	int	YES
modate	datetime	YES

sysusermsgs (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
msglangid	smallint	YES
severity	smallint	YES
status	smallint	YES
text	nvarchar (1024)	YES

syswebmethods (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
nmspace	nvarchar (384)	YES
alias	nvarchar (64)	YES
objname	nvarchar (776)	YES
status	int	YES

sysxlgns (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
sid	varbinary (85)	YES
status	int	YES
type	char (1)	YES
crdate	datetime	YES
modate	datetime	YES
dbname	nvarchar (128)	YES
lang	nvarchar (128)	YES
pwdhash	varbinary (256)	YES

sysxsrvs (master database only)

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
product	nvarchar (128)	YES
provider	nvarchar (128)	YES
status	int	YES
modate	datetime	YES
catalog	nvarchar (128)	YES
cid	int	YES
connecttimeout	int	YES
querytimeout	int	YES

sysallocunits

Column Name	Data Type	Is Nullable	Description
auid	bigint	YES
type	tinyint	YES
ownerid	bigint	YES
status	int	YES
fgid	smallint	YES
pgfirst	binary (6)	YES
pgroot	binary (6)	YES
pgfirstiam	binary (6)	YES
pcused	bigint	YES
pcdata	bigint	YES
pcreserved	bigint	YES

sysasymkeys

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
thumbprint	varbinary (32)	YES
bitlength	int	YES
algorithm	char (2)	YES
modified	datetime	YES
pkey	varbinary (2000)	YES
encrtype	char (2)	YES
pukey	image (2147483647)	YES

sysbinobjs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
nsid	int	YES
name	nvarchar (128)	YES
status	int	YES
type	char (2)	YES
intprop	int	YES
created	datetime	YES
modified	datetime	YES

sysbinsubobjs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
idmajor	int	YES
subid	int	YES
name	nvarchar (128)	YES
status	int	YES
intprop	int	YES

syscerts

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
issuer	varbinary (884)	YES
snum	varbinary (16)	YES
thumbprint	varbinary (32)	YES
pkey	varbinary (2000)	YES
encrtype	char (2)	YES
cert	image (2147483647)	YES
status	int	YES

sysclsobjs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
name	nvarchar (128)	YES
status	int	YES
type	char (2)	YES
intprop	int	YES
created	datetime	YES
modified	datetime	YES

syscolpars

Column Name	Data Type	Is Nullable	Description
id	int	YES
number	smallint	YES
colid	int	YES
name	nvarchar (128)	YES
xtype	tinyint	YES
utype	int	YES
length	smallint	YES
prec	tinyint	YES
scale	tinyint	YES
collationid	int	YES
status	int	YES
maxinrow	smallint	YES
xmlns	int	YES
dflt	int	YES
chk	int	YES
idtval	varbinary (64)	YES

sysconvgroup

Column Name	Data Type	Is Nullable	Description
id	uniqueidentifier	YES
service_id	int	YES
status	int	YES
refcount	int	YES

sysdbfiles

Column Name	Data Type	Is Nullable	Description
dbid	int	YES
fileid	int	YES
grpid	int	YES
status	int	YES
size	int	YES
maxsize	int	YES
growth	int	YES
lname	nvarchar (128)	YES
pname	nvarchar (260)	YES
createlsn	binary (10)	YES
droplsn	binary (10)	YES
filetype	tinyint	YES
filestate	tinyint	YES
fileguid	uniqueidentifier	YES
internalstatus	int	YES
readonlylsn	binary (10)	YES
readwritelsn	binary (10)	YES
readonlybaselsn	binary (10)	YES
firstupdatelsn	binary (10)	YES
lastupdatelsn	binary (10)	YES
backuplsn	binary (10)	YES
diffbaselsn	binary (10)	YES
diffbaseguid	uniqueidentifier	YES
diffbasetime	datetime	YES
diffbaseseclsn	binary (10)	YES
redostartlsn	binary (10)	YES
redotargetlsn	binary (10)	YES
forkguid	uniqueidentifier	YES
forklsn	binary (10)	YES
forkvc	bigint	YES
redostartforkguid	uniqueidentifier	YES

sysdercv

Column Name	Data Type	Is Nullable	Description
diagid	uniqueidentifier	YES
initiator	tinyint	YES
handle	uniqueidentifier	YES
rcvseq	bigint	YES
rcvfrag	int	YES
status	int	YES
state	char (2)	YES
lifetime	datetime	YES
contract	int	YES
svcid	int	YES
convgroup	uniqueidentifier	YES
sysseq	bigint	YES
enddlgseq	bigint	YES
firstoorder	bigint	YES
lastoorder	bigint	YES
lastoorderfr	int	YES
dlgtimer	datetime	YES
dlgopened	datetime	YES
princid	int	YES
outseskey	varbinary (56)	YES
outseskeyid	uniqueidentifier	YES
farprincid	int	YES
inseskey	varbinary (56)	YES
inseskeyid	uniqueidentifier	YES
farsvc	nvarchar (256)	YES
farbrkrinst	nvarchar (128)	YES

sysdesend

Column Name	Data Type	Is Nullable	Description
handle	uniqueidentifier	YES
diagid	uniqueidentifier	YES
initiator	tinyint	YES
sendseq	bigint	YES
sendxact	binary (6)	YES

sysfiles1

Column Name	Data Type	Is Nullable	Description
status	int	YES
fileid	smallint	YES
name	nchar (128)	YES
filename	nchar (260)	YES

sysftinds

Column Name	Data Type	Is Nullable	Description
id	int	YES
indid	int	YES
status	int	YES
crtype	char (1)	YES
crstart	datetime	YES
crend	datetime	YES
crrows	bigint	YES
crerrors	int	YES
crschver	binary (8)	YES
crtsnext	binary (8)	YES

sysguidrefs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
subid	int	YES
guid	uniqueidentifier	YES
status	int	YES

syshobtcolumns

Column Name	Data Type	Is Nullable	Description
hobtid	bigint	YES
hobtcolumnid	int	YES
status	int	YES
ordkey	smallint	YES
xtype	tinyint	YES
length	smallint	YES
prec	tinyint	YES
scale	tinyint	YES
collationid	int	YES
offsetleaf	smallint	YES
offsetint	smallint	YES
bitposleaf	tinyint	YES
bitposint	tinyint	YES
nullbitleaf	smallint	YES
nullbitint	smallint	YES

syshobts

Column Name	Data Type	Is Nullable	Description
hobtid	bigint	YES
status	int	YES
fillfact	tinyint	YES
maxnullbit	smallint	YES
maxleaf	int	YES
maxint	smallint	YES
minleaf	smallint	YES
minint	smallint	YES
lockres	bigint	YES

sysidxstats

Column Name	Data Type	Is Nullable	Description
id	int	YES
indid	int	YES
name	nvarchar (128)	YES
status	int	YES
intprop	int	YES
fillfact	tinyint	YES
type	tinyint	YES
tinyprop	tinyint	YES
dataspace	int	YES
lobds	int	YES
rowset	bigint	YES

sysiscols

Column Name	Data Type	Is Nullable	Description
idmajor	int	YES
idminor	int	YES
subid	int	YES
status	int	YES
intprop	int	YES
tinyprop1	tinyint	YES
tinyprop2	tinyint	YES

sysmultiobjrefs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
depid	int	YES
depsubid	int	YES
indepid	int	YES
indepsubid	int	YES
status	int	YES

sysnsobjs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
name	nvarchar (128)	YES
nsid	int	YES
status	int	YES
intprop	int	YES
created	datetime	YES
modified	datetime	YES

sysobjkeycrypts

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
thumbprint	varbinary (32)	YES
type	char (4)	YES
crypto	image (2147483647)	YES
status	int	YES

sysobjvalues

Column Name	Data Type	Is Nullable	Description
valclass	tinyint	YES
objid	int	YES
subobjid	int	YES
valnum	int	YES
value	nvarchar (4000)	YES
imageval	image (2147483647)	YES

sysowners

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
type	char (1)	YES
sid	varbinary (85)	YES
password	varbinary (256)	YES
dfltsch	nvarchar (128)	YES
status	int	YES
created	datetime	YES
modified	datetime	YES

sysprivs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
subid	int	YES
grantee	int	YES
grantor	int	YES
type	char (4)	YES
state	char (1)	YES

sysqnames

Column Name	Data Type	Is Nullable	Description
qid	int	YES
hash	int	YES
nid	int	YES
name	nvarchar (4000)	YES

sysremsvcbinds

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
scid	int	YES
remsvc	nvarchar (256)	YES
status	int	YES

sysrowsetcolumns

Column Name	Data Type	Is Nullable	Description
rowsetid	bigint	YES
rowsetcolid	int	YES
hobtcolid	int	YES
status	int	YES
rcmodified	bigint	YES
maxinrowlen	smallint	YES

sysrowsetrefs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
objid	int	YES
indexid	int	YES
rowsetnum	int	YES
rowsetid	bigint	YES

sysrowsets

Column Name	Data Type	Is Nullable	Description
rowsetid	bigint	YES
ownertype	tinyint	YES
idmajor	int	YES
idminor	int	YES
numpart	int	YES
status	int	YES
fgidfs	smallint	YES
rcrows	bigint	YES

sysrts

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
remsvc	nvarchar (256)	YES
brkrinst	nvarchar (128)	YES
addr	nvarchar (256)	YES
miraddr	nvarchar (256)	YES
lifetime	datetime	YES

sysscalartypes

Column Name	Data Type	Is Nullable	Description
id	int	YES
schid	int	YES
name	nvarchar (128)	YES
xtype	tinyint	YES
length	smallint	YES
prec	tinyint	YES
scale	tinyint	YES
collationid	int	YES
status	int	YES
created	datetime	YES
modified	datetime	YES
dflt	int	YES
chk	int	YES

sysschobjs

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
nsid	int	YES
nsclass	tinyint	YES
status	int	YES
type	char (2)	YES
pid	int	YES
pclass	tinyint	YES
intprop	int	YES
created	datetime	YES
modified	datetime	YES

sysserefs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
depid	bigint	YES
indepid	bigint	YES

syssingleobjrefs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
depid	int	YES
depsubid	int	YES
indepid	int	YES
indepsubid	int	YES
status	int	YES

syssqlguides

Column Name	Data Type	Is Nullable	Description
id	int	YES
name	nvarchar (128)	YES
scopetype	tinyint	YES
scopeid	int	YES
hash	varbinary (20)	YES
status	int	YES
created	datetime	YES
modified	datetime	YES

systypedsubobjs

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
idmajor	int	YES
subid	int	YES
name	nvarchar (128)	YES
xtype	tinyint	YES
utype	int	YES
length	smallint	YES
prec	tinyint	YES
scale	tinyint	YES
collationid	int	YES
status	int	YES
intprop	int	YES

sysxmitqueue

Column Name	Data Type	Is Nullable	Description
dlgid	uniqueidentifier	YES
finitiator	bit	YES
tosvc	nvarchar (256)	YES
tobrkrinst	nvarchar (128)	YES
fromsvc	nvarchar (256)	YES
frombrkrinst	nvarchar (128)	YES
svccontr	nvarchar (256)	YES
msgseqnum	bigint	YES
msgtype	nvarchar (256)	YES
unackmfn	int	YES
status	int	YES
enqtime	datetime	YES
rsndtime	datetime	YES
dlgerr	int	YES
msgid	uniqueidentifier	YES
hdrpartlen	smallint	YES
hdrseclen	smallint	YES
msgenc	tinyint	YES
msgbodylen	int	YES
msgbody	image (2147483647)	YES

sysxmlcomponent

Column Name	Data Type	Is Nullable	Description
id	int	YES
xsdid	int	YES
uriord	int	YES
qual	tinyint	YES
nameid	int	YES
symspace	char (1)	YES
nmscope	int	YES
kind	char (1)	YES
deriv	char (1)	YES
status	int	YES
enum	char (1)	YES
defval	nvarchar (4000)	YES

sysxmlfacet

Column Name	Data Type	Is Nullable	Description
compid	int	YES
ord	int	YES
kind	char (2)	YES
status	smallint	YES
dflt	nvarchar (4000)	YES

sysxmlplacement

Column Name	Data Type	Is Nullable	Description
placingid	int	YES
ordinal	int	YES
placedid	int	YES
status	int	YES
minoccur	int	YES
maxoccur	int	YES
defval	nvarchar (4000)	YES

sysxprops

Column Name	Data Type	Is Nullable	Description
class	tinyint	YES
id	int	YES
subid	int	YES
name	nvarchar (128)	YES
value	nvarchar (4000)	YES