Tuesday, January 27, 2015

MS-SQL Quick Hit: Listing Field Definitions

Some months back I wrote about some important performance gotchas pertaining to using SugarCRM on Microsoft SQL Server (MS-SQL). More recently I had a need to analyze a system to uncover the source of similar issues. 

While doing so, I came up with an SQL query that helped me quickly view all  the column names and their definitions, simplifying the process of identifying potentially problematic entries.

I share this query below in hopes it will help you at some point:

select,,, c.max_length, c.precision, c.scale, c.collation_name 
from sys.columns c
join sys.types t
on c.user_type_id = t.user_type_id
join sys.tables ta
on c.object_id = ta.object_id

Note that with some minor modifications you can get the query to list out the columns of a specific data type.


  1. Hi Angel,
    I ran into another query while trying to fix a Sugar bug. I documented the bug and the query on my blog at:

    The query allowed me to search across the tables in the database for fields set to the datetime data type. I thought your readers might be interested. Here's the query:

    "SELECT table_schema, table_name, column_name, ordinal_position
    FROM information_schema.columns
    WHERE table_schema = '[ your db name ]' and data_type in ('datetime')
    ORDER BY 2, 4;"

    With some modification, you could also use teh query to search across the entire database for values in a field

    Thanks for your postings, you've helped me out with tips in my day-to-day efforts. I appreciate.

    1. Cool. Glad I could help and thanks for sharing that tidbit as well!


Your comments, feedback and suggestions are welcome, but please refrain from using offensive language and/or berating others. Thank you in advance.