如何从表中获取列类型？

Question

I have this code:

select  a.id as tableid,
    a.name as tableName, 
    b.name as columnName,
    b.status as columnStatus,
    b.type as columnType
    from sysobjects a 
    LEFT JOIN syscolumns b
    ON a.id = b.id
    WHERE a.name = 'table_name'

现在，columType 显示为数字。我想获取该 columnType 的名称，该名称位于表 'systypes' 的列 'name' 中。我该如何做？简单的 LEFT JOIN 会导致重复行。

- Iyas

Answer 1

我在Sybase 15.0中看到过这段代码，以下是你需要使用的代码：

select o.id [tableid], o.name [tableName], c.name [columnName], c.status [columnStatus], t.name [columnType] from sysobjects o
inner join syscolumns c on c.id = o.id
inner join systypes t on t.usertype = c.usertype
where o.type = 'U' and o.name in ('tablename')

- aF.

---

在表systypes中，列'type'的值为39存在重复条目。这就解释了为什么使用您的代码会得到额外的行。当type = 39时，列'name'的值为'sysname'、'nvarchar'和'varchar'。 - Iyas

我的第二个答案在空间中丢失了！我从mssql语法中找到了类似的答案，唯一不同的是mssql使用xusertype。所以我接受你的答案。谢谢aF！ - Iyas

在where子句中使用usertype而不是type似乎提供了所需的结果。虽然这是一个旧的帖子，但为了确认是否有其他人遇到类似的问题，我还是要确认一下。 - Gentle

查询语句在 [columnStatus] 后缺少逗号。 - Tom Hanley

Answer 2

select o.id [tableid], o.name [tableName],
c.name [columnName], c.status [columnStatus],
t.name [columnType] from sysobjects o
inner join syscolumns c on c.id = o.id
inner join systypes t on t.type = c.type
where o.type = 'U' and o.name = 'table_name'
and t.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar')

我从@aF的代码中添加了最后一行。我不知道如何解释这个。我在这里找到了答案：http://www.dbforums.com/sybase/913004-getting-column-type.html#post3355703

- Iyas

---

那是错误的。我看过了，你必须将systypes类型定义为用户（U）并将其与usertype链接 :) - aF.

Answer 3

需要在type和usertype上都进行连接：

SELECT
 o.id     AS tableID,
 o.name   AS tableName,
 c.name   AS columnName,
 c.status AS columnStatus,
 t.name   AS typeName
FROM       syscolumns AS c NOLOCK
INNER JOIN systypes   AS t NOLOCK
 ON  c.type     = t.type
 AND c.usertype = t.usertype
INNER JOIN sysobjects AS o NOLOCK
 ON c.id = o.id
INNER JOIN sysusers   AS u NOLOCK
 ON o.uid = u.uid
WHERE o.name = 'table_name'
 AND  u.name = 'dbo'

- Michel de Ruiter

---

被接受的答案并没有给我正确的结果，但这个可以。 - Dan

Answer 4

select so.name as table_name, '' as table_description,sc.name as fileld_name,'' as field_description,
    st.name as format,
    sc.length as data_length,sc.prec as decimal_places,'' as primary_key,
     case when sc.status = 8 then 'Y'
            else 'N'
     end as nulls
    from syscolumns sc
    INNER JOIN sysobjects so ON sc.id = so.id
    inner join systypes st on sc.type = st.type
    WHERE so.name in (/*table list*/)
    and st.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar','longsysname','usr_char')
    order by so.name   
    go

- Lakshman

Answer 5

create function dbo.GetColumnDataTypeName(@TableName TName, @ColumnName TName)
returns sysname
as
begin
    declare @typeName sysname,
        @tableId int = object_id(@TableName)
    select
    @typeName = type_name(user_type_id)
    from sys.columns sc
    where sc.[object_id] = @tableId
    and sc.Name = @ColumnName

    return @typeName
end
Go

- massoud