CleanCode::SQL::GetDataTypeName

This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.

GetDataTypeName

GetDataTypeName is a small but handy function that returns the name of the data type of a particular field.

Usage and Parameters

N U T S H E L L
Module	GetDataTypeName
Description	Returns data type name for a given field
Environment	SQL Server 2000/2005
Author	Michael Sorens
Since	CleanCode 0.9.22

GetDataTypeName @TableName, @FieldName

where:
   @TableName      ::= table name
   @FieldName      ::= field name

@TableName [ Type: varchar(100) Default: -none- ]: Name of the table to examine.

@FieldName [ Type: varchar(100) Default: -none- ]: Name of the field to examine.

Returns: varchar(25): The name of the data type, or null if not found.

Details

GetDataTypeName looks up a table name and field name in the system tables sysobjects and syscolumns. If it finds a match to your parameters--and note they are case sensitive!--it returns the name of the data type of that field. If no match is found, the function returns null.

Note that this query does not take into account a schema name, if present. If you use the same table name in two different schemas you may get an invalid result (have not tested that). For example if you have two tables Sales.OrderDetails and Commission.OrderDetails (i.e. the table name OrderDetails is used in both the Sales schema and the Commission schema) GetDataTypeName may not find the correct match.

Examples

With reference to the standard AdventureWorks database:

EXAMPLET-SQL

Return money:

    select dbo.GetDataTypeName('PurchaseOrderHeader', 'SubTotal')

Return null because you may not qualify the table name with the schema name:

    select dbo.GetDataTypeName('HumanResources.EmployeePayHistory','ModifiedDate')

Return datetime:

    select dbo.GetDataTypeName('EmployeePayHistory','ModifiedDate')

Return null because the case of the field name parameter is not an exact match to the actual field name:

    select dbo.GetDataTypeName('HumanResources.EmployeePayHistory','modifieddate')