Collation Conflict on sysname? Really?

Okely dokely, here it is.

I have a database that has a differing collation to that of the tempdb. And obviously because of this I've run into problems when referencing table variables and temp tables.

BUT! Given the following example, what am I doing wrong, or is there no solution to this.

<Start Example>

/*================================================= ================================================== ==========================
fnPM_ForeignKey
================================================== ================================================== =========================*/

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnPM_ForeignKey]')) BEGIN
DROP FUNCTION [dbo].[fnPM_ForeignKey]
END
GO

CREATE FUNCTION fnPM_ForeignKey (@.ChildTable as sysname, @.ChildColumn as sysname)

RETURNS @.ForeignKey TABLE (
FKName sysname,
ParentTable sysname,
ParentColumn sysname,
DescriptionColumn sysname COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN

INSERT INTO @.ForeignKey (FKName, ParentTable, ParentColumn)
SELECT FK.Name, Parent.Name, ParentCol.Name
FROM sysforeignkeys
INNER JOIN sysobjects FK on sysforeignkeys.constid = FK.ID
INNER JOIN sysobjects Parent on sysforeignkeys.rkeyid = Parent.id
INNER JOIN syscolumns ParentCol on ParentCol.id = Parent.id and sysforeignkeys.rkey = ParentCol.colid
INNER JOIN sysobjects Child on sysforeignkeys.fkeyid = Child.id
INNER JOIN syscolumns ChildCol on ChildCol.id = Child.id and sysforeignkeys.fkey = ChildCol.colid
WHERE Child.Name = @.ChildTable and ChildCol.Name = @.ChildColumn

--> UPDATE @.ForeignKey Set DescriptionColumn = syscolumns.Name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM @.ForeignKey ForeignKey
INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
INNER JOIN syscolumns on syscolumns.id = sysobjects.id
WHERE syscolumns.Name like '%Name%'

UPDATE @.ForeignKey Set DescriptionColumn = syscolumns.Name
FROM @.ForeignKey ForeignKey
INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
INNER JOIN syscolumns on syscolumns.id = sysobjects.id
WHERE ForeignKey.DescriptionColumn is null and syscolumns.Name like '%Description%'

RETURN

END
GO

< end example >

So here I'm defining my function which basically returns the parent table details of a given foreign key relationship. But when trying to run the above script I get this,

Server: Msg 446, Level 16, State 9, Procedure fnPM_ForeignKey, Line 22
Cannot resolve collation conflict for equal to operation.

First up I find this weird because isn't sysname equvalent to nvarchar(128)? And aren't nvarchar fields independant of collation? But it gets better,

I thought, ok maybe I'm wrong about sysname needing collation, so I figured I'll check the collation of the syscolumns.name column,

Heres the query

select sysobjects.Name, syscolumns.name, syscolumns.collation
from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name ='syscolumns' and syscolumns.name = 'name'

This is what it returns,

syscolumns name SQL_Latin1_General_CP1_CI_AS

So, let me get this straight, I've explicitly stated that the table variable column is collation SQL_Latin1_General_CP1_CI_AS and the column I'm comparing it to also has a collation of SQL_Latin1_General_CP1_CI_AS, and it doesn't work. . . .

I smell Microsoft, or is there something equally as hideous going on here.

Regards

Adam.Under SQL 2000, a table variable is collated the same as tempdb. Under SQL 2005, table variables (and temp tables) are collated the same as the current DB context. In order to fix this, just add a collate statement to any join involving the table variable, and a system table. (in the above statement, you have the collate statement on the assignment)

The collation is part code page, and mainly sort order. The code page part is the only part that nvarchar, nchar and ntext are "immune" to. They are still bound by the sort order and comparison rules. In a case sensitive or binary collation, "A" does not equal "a".

Clear as mud?