Understanding sysobjects table in SQL Server

System tables and views are very useful objects that may be queried in many times by database background processes itself or by DBAs. These objects sometimes contain columns that are not so easy to understand what they are for. Here, in this post I will discuss  mostly used content of SYSOBJECT table.

Take into the consideration, that updating or deleting rows in these tables are not recommended. Background processes do it for you.

The most useful columns in SYSOBJECT table are name, id, xtype, uid, parent_obj, crdate.

As you can guess name column saves the name of the database object. Id column is the unique number for this object. xtype shows the type of this object, where:

• AF = Aggregate function (CLR)
• C = CHECK constraint
• D = Default or DEFAULT constraint
• F = FOREIGN KEY constraint
• L = Log
• FN = Scalar function
• FS = Assembly (CLR) scalar-function
• FT = Assembly (CLR) table-valued function
• IF = In-lined table-function
• IT = Internal table
• P = Stored procedure
• PC = Assembly (CLR) stored-procedure
• PK = PRIMARY KEY constraint (type is K)
• RF = Replication filter stored procedure
• S = System table
• SN = Synonym
• SQ = Service queue
• TA = Assembly (CLR) DML trigger
• TF = Table function
• TR = SQL DML Trigger
• TT = Table type
• U = User table
• UQ = UNIQUE constraint (type is K)
• V = View
• X = Extended stored procedure

I’ve got this list from the internet,most of them are useful to know.

uid                – saves the user id, which is the owner of this object.
parent_obj – Id of the parent object. For example, for constraint parent_obj will be the ID
                        of the table, on which this constraint was defined.
crdate         -Object creation date.
…

There are several other tables, which shows the user objects:

–Shows the table columns owned by the current user.

select *
from information_schema.columns

–Shows the tables owned by the current user.

select *
from information_schema.tables

–Shows the list of all functions and procedures owned by the current user.

select *
from information_schema.routines

Real World Scenario

One day, the person came to me and asked if it was possible to find the table name when you just know the column name. I told him yes and wrote the following simple query:

select a.name as columnName
           ,b.name as tablename
from SYSCOLUMNS as a,SYSOBJECTS as b
where a.ID=b.ID
and a.name=’column_name’

I hope this post was useful for you…