Find out foreign key reference table

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:

select *
from sysobjects so inner join syscolumns sc on so.id = sc.id 
where sc.name = 'field name'

you can also check out the sybase books online (system tables)

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:

• can't have filtered foriegn keys
• can't FK to constants
• can't have multiple parents

Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand

CREATE TABLE OutlineFilesCostCentre (
    CostCentreCode ...NOT NULL --PK
    ...
    )

CREATE TABLE OutlineFilesLocations (
    LocationCode ... NOT NULL --PK
    ...
    )

CREATE TABLE Assets (
    ...
    CostCentreCode ... NOT NULL,
    LocationCode ... NOT NULL,
    ...

    CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
                REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
    CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
                REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
   ...)

If you have 30 codes to lookup you will have 30 lookup tables: this is correct.

If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.

    ...
    CostCentreType char(2) NOT NULL,
    CostCentreCode ... NOT NULL,
    LocationType  char(2) NOT NULL,
    LocationCode ... NOT NULL,
    ...

Or use triggers to maintain the correct codes. I wouldn't do this either.