Pages

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.name, t.name, ta.name, 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.