According to the sysobjects documentation, sysobjects.xtype
can be one of these object types:
| xtype | Description |
|-------|---------------------------------------|
| 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 |
and I could put those into a CASE
statement, but is there a table I can just join on to lookup that xtype
description? I know systypes
isn't that table. I mean, I've just kind of memorized a lot of them, but I'm doing some research on a database and it's foreign to me (i.e. I don't know a ton about it) and so I'd like to build that description into this query without a CASE
statement:
select object_name(c.id), c.name, [length], o.xtype from syscolumns c
join sysobjects o on o.id = c.id
where c.name like '%job%code%'
Update
Below is the end result after the answer by SQLMenace. I felt it necessary to place here because it's not just a straight forward join
.
select object_name(c.id), c.name, t.name, c.[length], o.xtype, x.name from syscolumns c
join sysobjects o on o.id = c.id
join systypes t on t.xtype = c.xtype
join master..spt_values x on x.name like '%' + o.xtype + '%' and x.type = 'O9T'
where c.name like '%job%code%'
order by c.xtype
sysobjects system view is the XTYPE which contains the type of object a particular row contains. The value in the XTYPE column can be one of the following object types: AF : Aggregate function (CLR) C : CHECK Constraint.
We can use system catalog view sys. objects to view all objects in a SQL database. It has a column type that contains the object category. For example, if we want to search only for the user-defined table, we use 'U' value for the type column.
dbo. sysobjects is a SQL 2000 construct that is only in SQL 2008 for backward-compatibility reasons. sys. objects contains a row for each user-defined, schema-scoped object that is created within a database, so you wouldn't have to filter your query at all.
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 .)
There is this
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
Output
AF: aggregate function
AP: application
C : check cns
D : default (maybe cns)
EN: event notification
F : foreign key cns
FN: scalar function
FS: assembly scalar function
FT: assembly table function
IF: inline function
IS: inline scalar function
IT: internal table
L : log
P : stored procedure
PC : assembly stored procedure
PK: primary key cns
R : rule
RF: replication filter proc
S : system table
SN: synonym
SQ: queue
TA: assembly trigger
TF: table function
TR: trigger
U : user table
UQ: unique key cns
V : view
X : extended stored proc
sysobjects.type, reports
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With