Using INFORMATION_SCHEMA to Get Column Information

A commonly asked question that I field is when working with CRM or another 3rd party database what is the most efficient way in order to find out column information on a specific table using only T-SQL. This practice is often important if you are writing your own stored procedures and functions to import data into the database. This is often done by writing a series of views that will be used from the import source that map the table structure that the data is going into. So you would naturally want to write some T-SQL that would just automatically create the INSERT statements from view source A into destination table B.

*Disclaimer: Although you could do the same type of thing using SSIS it may not be an option in your environment or you may not consider that option as flexible.

This is readily accomplished by using the INFORMATION_SCHEMA. In the example below I have a custom entity in my CRM instance called Employee. Now in CRM you always have a Base table and then an ExtensionBase table. The ExtensionBase table for an entity is normally the place where you are looking at because your looking for attributes. So the query below should return me decent information about the custom attributes that have been created for me in the table…

SELECT 
COLUMN_NAME,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='new_EmployeeExtensionBase'
ORDER BY 
COLUMN_NAME;

Cheers,

AJ