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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy