Multiple entries in syscolumns for each column of type 'geography'

Question

First, I have created a table called Placemarks containing a column of type 'geography'.

CREATE TABLE [dbo].[Placemarks](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Location] [geography] NOT NULL,
CONSTRAINT [PK_Placemarks] 
    PRIMARY KEY CLUSTERED([ID] ASC)
)

Then, I use the following query in a stored procedure to get a list of all columns in the table with their data types.

SELECT 
   b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a 
inner join syscolumns b on a.id = b.id 
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname' 
WHERE a.id = object_id(N'[dbo].[Placemarks]') 
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
ORDER BY b.colId

The result of the query can be viewed here:

I am using this query in a stored procedure and need to get a single row for each column in my Placemarks table. I could filter out rows with TypeName = geometry or hierarchyid.

But I may use the geometry datatype in the future and want the query to be forward compatible. Any other ideas?

Answer 1

The additional rows are being brought in by the join on systypes. Changing the join condition to

inner join systypes c on b.xtype = c.xtype and b.xusertype=c.xusertype

seems to work. You should use sys.columns, sys.types etc. instead of the deprecated syscolumns, systypes backward compatibility views.