A column of database type “geometry” results in two extra columns hidden from ssms

All we need is an easy explanation of the problem, so here it is.

If I create a table like

create table test2 (foo geometry)

foo will show up in the table designer as expected

A column of database type “geometry” results in two extra columns hidden from ssms

but when I query sys.columns for column information…

select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.system_type_id = ty.system_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

…I also got a columns of the types hiearchyid and geography (using the same column name).

A column of database type “geometry” results in two extra columns hidden from ssms

Why?

Can I change my sys.columns query to filter out those pseudo columns?

(I am using that query to recreate a create table query).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

use this query instead:

select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.user_type_id = ty.user_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

the join is based on the user_type_id so the results are the same as the GUI.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply