How to find the type, length, nullable or not given a table and a field name

Question

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)

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

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.

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.

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?

Answer 1

You want to query the INFORMATION_SCHEMA.Columns special table, and filter by the columns TABLE_NAME and 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.