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

CleanCode logo
NAVIGATION: first page in sectionprevious pagenext pagefinal page in section


DescriptionReturns data type name for a given field
EnvironmentSQL Server 2000/2005
AuthorMichael Sorens
SinceCleanCode 0.9.22

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

Usage and Parameters

GetDataTypeName @TableName, @FieldName

   @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.


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.


With reference to the standard AdventureWorks database:

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')

Get CleanCode at Fast, secure and Free Open Source software downloads
Copyright © 2001-2013 Michael Sorens • Contact usPrivacy Policy
Usage governed by Mozilla Public License 1.1 and CleanCode Courtesy License
CleanCode -- The Website for Clean DesignRevised 2013.06.30