如何找到类型，长度，可空或不给定表和字段名称

I have a table that has data that maps to fields in other tables in the database. Something like this:

我有一个表,其数据映射到数据库中其他表中的字段。像这样的东西:

FieldName       isNeeded
CUSTNAME        0
PRODQTY         1

The MYFIELD1 and MYFIELD2 maps to fields in other tables. In a SQL Script, I would like to get the field definition such as type, length (info that we get we run a sp_help on a table)

MYFIELD1和MYFIELD2映射到其他表中的字段。在SQL脚本中,我想获得字段定义,例如类型,长度(我们得到的信息是在表上运行sp_help)

So I am planning to add 2 other varchar fields to this table to make it look like this

所以我打算在这个表中添加2个其他varchar字段,使它看起来像这样

FieldName      isNeeded  SourceTableThisMapsTo  SourceFieldThisMapsTo
CUSTNAME       0         Customer               name
PRODQTY        1         Products               quantity

This way, I can get Customer.name as a varchar in a sql script.

这样,我可以在sql脚本中将Customer.name作为varchar。

What SQL command can i run that gives me all the information about the Customer.name field (such as datatype = varchar(200), nullable = no, precision = 10 etc) and how do I use this information? Any example is really appreciated.

我可以运行哪个SQL命令,它提供了有关Customer.name字段的所有信息(例如datatype = varchar(200),nullable = no,precision = 10等)以及如何使用此信息?任何一个例子都非常感激。

My plan is to store the table and column name instead of the datatype as the datatype might be changed in the future and someone should remember to update this table (which could be a maintainenace issue)

我的计划是存储表和列名而不是数据类型,因为将来可能会更改数据类型,有人应该记得更新此表(这可能是维护问题)

Also, I inherited this table from someone else, Is this a standard way of doing it?

另外,我从别人那里继承了这个表,这是一个标准的做法吗?

1 个解决方案

#1

You want to query the INFORMATION_SCHEMA.Columns special table, and filter by the columns TABLE_NAME and COLUMN_NAME:

您想要查询INFORMATION_SCHEMA.Columns特殊表,并按TABLE_NAME和COLUMN_NAME列进行筛选:

SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'table'
AND COLUMN_NAME = 'column'

Take a look at the output and pick and choose which columns you'll need. Most likely you'll want IS_NULLABLE, DATA_TYPE and NUMERIC_PRECISION.

查看输出并选择您需要的列。很可能你会想要IS_NULLABLE,DATA_TYPE和NUMERIC_PRECISION。

#1