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.
Hi Angel,
ReplyDeleteI ran into another query while trying to fix a Sugar bug. I documented the bug and the query on my blog at: http://www.geekgumbo.com/2015/02/09/sugarcrm-datetime-bug/
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.
Dale
Cool. Glad I could help and thanks for sharing that tidbit as well!
Delete