July 31, 2002 at 8:24 am
I am looking for a way to get the name of a column name of a table from the id of the column (the 1st column or the 2nd column, etc.) The reason I need this is because the SYSFOREIGNKEYS table contains FKEY which is the "ID of the referencing column". I need to get the "Name of the referencing column". The only way I can think to do this is to create a temp table with an identity column and fill it with the SP_COLUMNS stored proc for the table containing the column and then select the column name from the FKEY (id).
Any ideas???
Thanks.
July 31, 2002 at 8:48 am
You'll need to join two system tables, sysobjects and syscolumns. To get the right name for the column, you'll have to know the id of the object. If you know the name of the table, you can obtain the id from sysobjects. You'll also need to know the colid (the id of the column), which it looks like you have. Here's an example where I'm substituting with variables to make lookups easier:
-- Variables we'll use to put values in
-- for our lookup
DECLARE @Table sysname
DECLARE @ColID int
-- set the variables to match what we
-- are looking up
SET @Table = 'sysobjects'
SET @ColID = 2
-- Perform the search
SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so
ON sc.id = so.id
WHERE sc.colid = @ColID
AND so.name = @Table
With the variables set as they are, this should return the column "id" because that's the column with colid = 2 in the sysobjects table.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 31, 2002 at 11:04 am
Yes, of course. Thank you very much, that's perfect.
-- Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply