April 23, 2003 at 2:42 pm
This has turned out to be more of a challenge than I thought. I'm sure I'm making it harder than it should be, so I'm looking for suggestions.
Thank YOU
April 23, 2003 at 2:57 pm
Well, the fastest way is to execute:
EXEC sp_pkeys [TableName].
This will show you the fields of the pk
April 23, 2003 at 3:08 pm
another elementary question, I'm sure, but how would you then capture and inspect the results of the SP?
Thanks
P.S. I should clarify, that I'm attempting to do this in a stored procedure, and that I will most likely be building a dynamic SQL string to be executed.
Edited by - joshcsmith13 on 04/23/2003 3:10:17 PM
April 24, 2003 at 4:12 am
This will give you just the column names from the PK. It was stripped from the sp_pkeys Proc.
select
convert(sysname,c.name) as COLUMN_NAME
from
sysindexes i
INNER JOIN
syscolumns c
ON
c.id = i.id
where
(i.status & 0x800) = 0x800 and
c.name IN (
index_col ('tblnamehere', i.indid, 1),
index_col ('tblnamehere', i.indid, 2),
index_col ('tblnamehere', i.indid, 3),
index_col ('tblnamehere', i.indid, 4),
index_col ('tblnamehere', i.indid, 5),
index_col ('tblnamehere', i.indid, 6),
index_col ('tblnamehere', i.indid, 7),
index_col ('tblnamehere', i.indid, 8),
index_col ('tblnamehere', i.indid, 9),
index_col ('tblnamehere', i.indid, 10),
index_col ('tblnamehere', i.indid, 11),
index_col ('tblnamehere', i.indid, 12),
index_col ('tblnamehere', i.indid, 13),
index_col ('tblnamehere', i.indid, 14),
index_col ('tblnamehere', i.indid, 15),
index_col ('tblnamehere', i.indid, 16)
)
April 24, 2003 at 3:21 pm
now that's some good crap there. I forgot that you can spy on SQL Server to see how it does those sp_'s
Thanks Antares.
April 25, 2003 at 1:59 am
Or you could put the results of the proc in a temp table and process from there, eg
create table #t (TABLE_QUALIFIER varchar(255),TABLE_OWNER varchar(255),TABLE_NAME varchar(255),COLUMN_NAME varchar(255),KEY_SEQ int,PK_NAME varchar(255))
INSERT INTO #t
EXEC sp_pkeys [TableName]
SELECT * FROM #t
DROP TABLE #t
Edited by - davidburrows on 04/25/2003 01:59:14 AM
Edited by - davidburrows on 04/25/2003 01:59:58 AM
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2003 at 6:32 am
May 2, 2003 at 10:24 am
look for "OBJECTPROPERTY" in Books on-line
paul
paul
May 7, 2003 at 4:24 am
I propose :
select column_name
from information_schema.key_column_usage
where table_name = ...
Jacques.
May 8, 2003 at 12:19 pm
Here is a view I created similar to Antares without the huge IN clause...
It does require a numbers (sequence) table though.
CREATE VIEW dbo.PK_Columns_View
AS
selecto.nameASname,
o.idASid,
c.nameAScolumn_name,
n.idASsequence_number
FROMsysobjectsASo
JOINsysindexesASi
ONo.id = i.id
AND(i.status & 0x800) = 0x800
JOINsyscolumnsASc
ON c.id = o.id
JOIN numbers as n
ON n.id <= i.keycnt
ANDc.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, n.id)
WHEREo.type in ('U')
ANDpermissions(o.id) <> 0
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply