在哪里可以找到列数据类型的Sql Server元数据? [英] Where do I find Sql Server metadata for column datatypes?

问题描述

我知道我可以通过以下方式访问列属性:

I know that I can get access to column properties via:

select * 
from sysobjects

但是我找不到关于在哪里获取列的类型和类型长度的信息，即:

What I can't find however is information about where to get the type and type length for a column, ie: in

FOO VARCHAR(80)

在哪里可以找到元数据表中类型声明的"VARCHAR(80)"部分?

Where do I look to find the "VARCHAR(80)" part of the type declaration in the metadata tables?

我尝试查看systypes表，但是其xtype的值与sysobjects表中的xtype的值不匹配.

I tried looking at the systypes table, but its values for xtype do not match up to the values of xtype in the sysobjects table.

*我无权访问用于构建这些表的原始SQL，也没有任何管理员权限.

*I do not have access to the original SQL used to build these tables nor do I have any admin rights.

如果您熟悉DB2，我正在寻找与

If you're familiar with DB2 I'm looking for the equivalent to

select name,
       coltype,
       length,
  from sysibm.syscolumns
where tbname = 'FOO'

推荐答案

您很亲密.您可以查看sys.columns以获得列.

You are close. You can look at sys.columns to get the columns.

您可以使用OBJECT_ID=OBJECT_ID('dbo.Foo')在表上进行过滤.

You can filter on a table with OBJECT_ID=OBJECT_ID('dbo.Foo').

您可以从sys.columns获取长度.数据类型在system_type字段中.该字段的键在sys.types中.

You can get the length from sys.columns. The data type is in the system_type field. The keys for that field are in sys.types.

您可以完整地进行以下操作:

In its entirety you can do:

select object_NAME(c.object_id), c.name, t.name, c.max_length
from sys.columns c
INNER JOIN sys.types t
    ON t.system_type_id = c.system_type_id

作为旁注，在SQL Server中，不建议使用系统表(即syscolumns，sysobjects)，建议最佳做法是改为使用视图sys.columns，sys.objects等.

As a side note, in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects) and it's recommended as a best practice to use the views instead, sys.columns, sys.objects, etc.

这将为您提供每个表格，表，列，数据类型和最大长度.

This will give you Table, column, data type, and maxlength for each one.

这篇关于在哪里可以找到列数据类型的Sql Server元数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！