RE: Odd behaviour from sys.dependencies

SSC Veteran

Points: 243

April 4, 2013 at 3:54 pm

Ok, done some investigation on column collation. I've cross referenced sys.columns on the object_id and column_id like so:

SELECT DISTINCT

Ref.referenced_database_name AS ImportDatabase

,Ref.referenced_schema_name AS ImportViewSchemaName

,SUBSTRING(Ref.referenced_entity_name,

(CHARINDEX('_', Ref.referenced_entity_name) + 1),

LEN(Ref.referenced_entity_name)) AS TableName

,SUBSTRING(Ref.referenced_entity_name, 1,

(CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName

,Ref.referenced_minor_name AS ImportColumn

,collation_name

FROM

sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding',

'OBJECT') AS Ref

LEFT JOIN sys.columns sysc

ON COLUMNPROPERTY(OBJECT_ID(Ref.referenced_schema_name + '.'

+ Ref.referenced_entity_name),

Ref.referenced_minor_name, 'ColumnId') = sysc.column_id

AND OBJECT_ID(Ref.referenced_schema_name + '.'

+ Ref.referenced_entity_name) = sysc.[object_id]

WHERE

1 = 1

AND Ref.referenced_minor_name IS NOT NULL

AND ref.referenced_schema_name = 'import'

Which returns the following when all values are populated correctly:

ImportColumncollation_name

PCCVRTLatin1_General_CI_AS

PCDIRELatin1_General_CI_AS

PCINRTLatin1_General_CI_AS

PCIREFNULL

PCORUVNULL

PCSRRTLatin1_General_CI_AS

PDACTIONLatin1_General_CI_AS

PDINVTNULL

PDIREFNULL

PDMULTNULL

PDSERLNULL

PDUACTIONNULL

PDUVALNULL

RPCOUPNULL

RPREFNULL

RBDATENULL

RBRATNULL

RBTITLLatin1_General_CI_AS

UNAMOUNTNULL

UNINSTREFNULL

UNMULTNULL

UNPOSTEDNULL

UNSERLNULL

UNTRANLatin1_General_CI_AS

Obviously, when rogue characters returned, result set is different:

ImportColumncollation_name

????RTNULL

????UVNULL

????EFNULL

????RENULL