根据
the sysobjects documentation,sysobjects.xtype可以是这些对象类型之一：
| 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 |
我可以把它们放入一个CASE语句中,但是我可以加入一个表来查找这个xtype描述？我知道systypes不是那个表.我的意思是,我刚刚记得很多,但我正在对数据库进行一些研究,对我来说是外来的(即我不了解它),所以我想建立该描述到这个查询没有CASE语句：
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%'
更新
以下是sqlMenace答案后的最终结果.我觉得有必要放在这里,因为它不只是一个直接的加入.
select object_name(c.id),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
解决方法
有这个
SELECT name FROM master..spt_values WHERE type = 'O9T'
产量
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
