How to query for primary keys
-
Is it possible to query the SQL Server System tables for a database (SYSOBJECTS, SYSCOLUMNS, etc) to find out which columns represent the primary key(s) of any given user table in the database? How might it be done?
I have an application where I need to do this.
Thanks!
Mark
-
You could do something like this:
select * from sysobjects
where xtype = 'PK' and parent_obj =
(select id from sysobjects where name = 'TableName')
**ASCII stupid question, get a stupid ANSI !!!** -
I hope this help!
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@objname varchar(776),
@dbname sysname
select @objname = 'USER_TABLE_NAME' -- <<------------ENTER TABLE NAME HERE
select @objid = object_id(@objname)
select @dbname = parsename(@objname,3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
RETURN
end
if @objid is NULL
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
RETURN
end
declare ms_crs_ind cursor local static for
select si.indid, si.groupid, si.name, si.status
from sysindexes si, sysobjects so
where so.type = 'U'
and so.name = @objname
and si.id = so.id
and si.name like 'PK__%' --remove this line if you want to get columns names from indexes
and si.id = @objid and si.indid > 0 and si.indid < 255 and (si.status & 64)=0
order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1) --'Object does not have any indexes.'
end
-- create temp table
create table #spindtab
(
index_name sysname collate database_default NOT NULL,
table_name varchar(500) NOT NULL,
groupname sysname collate database_default NOT NULL,
index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr
seq int
 
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
select @groupname = groupname from sysfilegroups where groupid = @groupid
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @seq int, @thiskey nvarchar(131) -- 128+3
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') <> 1)
begin
select @seq = 1
insert into #spindtab values (@indname, @objname, @groupname, @keys, @seq )
end
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') <> 1))
begin
select @seq = @seq + 1
insert into #spindtab values (@indname, @objname, @groupname, @thiskey, @seq)
end
while (@thiskey is not null )
begin
--select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @i = @i + 1, @seq = @seq + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') <> 1))
begin
insert into #spindtab values (@indname, @objname, @groupname, @thiskey, @seq)
end
end
-- INSERT ROW FOR INDEX
--insert into #spindtab values (@indname, @status, @groupname, @keys)
-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind
select * from #spindtab
drop table #spindtab
--drop table #spindtab
-
I appreciate the kind replies to my question. Unfortunately neither one seems to help. I have no rows in my sysobjects table that have an xtype beginning with 'PK'. I don't see any code in the second reply that helps.
Does someone else have a throught? My original question is:
Is it possible to query the SQL Server System tables for a database (SYSOBJECTS, SYSCOLUMNS, etc) to find out which columns represent the primary key(s) of any given user table in the database? How might it be done?
I have a table. I want to know which columns are primary key columns.
Thanks!
Mark
-
Are you running this in QA ?! My query should work if you're connected to the right datbase - ie, the database that has the table that you want to query...
Do you get any error messages or no resultset ?!
**ASCII stupid question, get a stupid ANSI !!!** -
This may seem silly to suggest - but please check the spelling of the table name carefully - I just tried it with a misspelled table name and it did not throw up any errors....
ps:You know for a fact that this table has a primary key ?!
**ASCII stupid question, get a stupid ANSI !!!** -
If you don't have any row in SysObjects of type PK, then you have no primary key in that database, you could also extend the search to 'UQ' for unique constraints that could also server as primary key.
-
How about if you run it without the xtype = 'PK' filter ?! What do you get then ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Or a best version to rule out the query completely :
exec sp_helpindex 'TableName'
-
Or even more specifically:
EXEC sp_columns @table_name = 'TableName'
**ASCII stupid question, get a stupid ANSI !!!** -
There's less irrelevant (in this case) information in sp_helpindex
. -
Remi - why did I know you'd come back with this ?!?! Just listing all columns is all...in case there're no indexes etc...HTH! Amen!
**ASCII stupid question, get a stupid ANSI !!!** -
You don't seem hth
. -
huh ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
.. just the way I understood the message...
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply