Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Interpreting type codes in sys.objects in SQL Server

On SQL Server, the sys.objects table includes "Type" and "Type_Desc" attributes. For example, for one of my DBs:

SELECT DISTINCT [Type], Type_Desc
FROM Sys.Objects
ORDER BY [Type]

Returns:

C       CHECK_CONSTRAINT  
D       DEFAULT_CONSTRAINT  
F       FOREIGN_KEY_CONSTRAINT  
FN      SQL_SCALAR_FUNCTION  
FS      CLR_SCALAR_FUNCTION  
IT      INTERNAL_TABLE  
P       SQL_STORED_PROCEDURE  
PK      PRIMARY_KEY_CONSTRAINT  
S       SYSTEM_TABLE  
SQ      SERVICE_QUEUE  
TR      SQL_TRIGGER  
U       USER_TABLE  
UQ      UNIQUE_CONSTRAINT  
V       VIEW  

Different DBs have different results, depending on what types are used.

Is there a comprehensive list of these types somewhere? There isn't a constraint on sys.objects that points me to table of these, and sys.types contains data types. I've searched SQL BOL but haven't found it. Any help would be appreciated.

EDIT: Some DBs use only a subset of these types. For example, if I have a database with no views, when I query Sys.Objects as above, there are no "V" rows in the results. I am looking for a list of all possible types and descriptions used by SQL Server.

like image 656
Justin R. Avatar asked May 25 '10 17:05

Justin R.


People also ask

What is SYS objects in SQL Server?

SYS. SYSOBJECTS contains a row for every object that has been created in the database, including stored procedures , views , and user tables (which are an important to distinguish from system tables .)

What are different types of objects in SQL?

SQL objects are schemas, journals, catalogs, tables, aliases, views, indexes, constraints, triggers, sequences, stored procedures, user-defined functions, user-defined types, global variables, and SQL packages. SQL creates and maintains these objects as system objects.

What is type U in Sysobjects?

U = User table. UQ = UNIQUE constraint (type is K) V = View. X = Extended stored procedure. uid.


1 Answers

BOL has a complete list, but you can't join on that.

AF = Aggregate function (CLR)
C  = CHECK constraint
D  = DEFAULT (constraint or stand-alone)
F  = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P  = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R  = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S  = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U  = Table (user-defined)
UQ = UNIQUE constraint
V  = View
X  = Extended stored procedure

Going to the best SQL Server source for this info: sys.objects (Transact-SQL) it doesn't mention any table to join to. I can't say that I have ever noticed a codes table to join to for any of the systems tables or views.

I guess you'll have to create your own table or view, or just use the Type_Desc attribute in sys.objects.

like image 66
KM. Avatar answered Sep 21 '22 12:09

KM.