A couple weeks back someone asked me some questions about data types.  Apparently, while implementing some financial data, each developer on the project had chosen their own data type.  This was a bit of a problem that can lead to serious problems.  When financial data is rounded unexpectedly – bad things can sometimes happen and it usually doesn’t involve plots for half pennies.

Anyways, I advised that it might be a good idea to get all of the data types on these columns aligned. To do this I provided them with a couple scripts that are below that they used to find the occurrences of this information.  These scripts both use the sys.columns and sys.types catalog views.

This first script, pulls out all of the columns that are in the database using decimal, numeric, or money data type.  It includes a filter where scale is not equal to zero – which should have included all of the financial data.

SELECT OBJECT_NAME(c.object_id) as table_name
    , c.name
    , t.name
    , c.precision
    , c.scale
FROM sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name IN ('decimal','numeric','money')
AND c.scale <> 0
ORDER BY 1, 2

The second script lists all of the columns of a specific name with their table and data type information.

SELECT OBJECT_NAME(c.object_id) as table_name
    , c.name
    , t.name
    , c.precision
    , c.scale
FROM sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.name = 'ListPrice'
ORDER BY 1, 2

Through the use of these and similar scripts, columns and the tables that they are in can be easily determined.  And this information can be found using either the data type or the name of the column.

Related posts:

1. Index Those Foreign Keys
2. Find Tables with Forwarded Records
3. Webcast Next Week – Performance Impacts Related to Different Function Types