Correct way of finding a tables primary keys?

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.

I thought that this code did the trick.

***

DECLARE @.c varchar(4000), @.t varchar(128)
SET @.c = ''
SET @.t='contact_pmc_contact_relations'
Select @.c = @.c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @.t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@.c, 1, Datalength(@.c) - 1)

***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.

Here is a dump from one of the tabels where it doesn't work.

SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table

***

id indid colid keyno

933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.

If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards

/Anders

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

|||

SQL Server 2000 ou 2005 ?

|||Currently we'r using sql server 2000, but if there is a way that works on both 2000 and 2005 it would be really great!|||

Madhu K Nair wrote:

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

Either im missunderstanding or that doesn't work. I want to get the columns that compose the primarykey. Not the primary key itself.

|||

i got the issue. Since there is no direct method (Single select statement), I will give u a workaround. If u check sp_helpconstraint , it give u the details. U can use sp_helptext to find the sourcecode behind this SP and do the required modification adn create u r own SP.

Madhu

|||

select * from INFORMATION_SCHEMA.[CONSTRAINT_COLUMN_USAGE]

where constraint_type='PRIMARY KEY'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is this you are looking for?

Code Snippet

SELECT CCU.TABLE_NAME

, CCU.COLUMN_NAME

, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON TC.TABLE_NAME = CCU.TABLE_NAME

AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY CCU.TABLE_NAME, ORDINAL_POSITION

Just expanding to what others meant to answer to your question.

Correalated sub...

i have a historical tale with seven different columns in it. I would like to join to this table and get the latest data for a particular key in it, the problem is one row in the historical table may only have one updated column in which case i need a compilatoun of several different rows to make the 'latest' row.(i.e. one updated row may only update one field...) i think i know what i want to do code follows... but it doesnt work... any ideas?
SELECT
CTRP.CUSIP as CUSIP,
CTRP.PORTFOLIO_NAME as PORTFOLIO,
CF.FACTOR * TRCURORIGFACE as CUR_FACE,
CF.FACTOR as CUR_FACTOR,
CPA.PX_ASK as CUR_PX_ASK,
CPS.PX_SPD AS CUR_PX_SPD,
CPSD.PX_SPD_DT AS CUR_PX_SPD_DT,
DMB.DISC_MRGN_BID AS DISC_MRGN_BID,
CWC.WAL_CALL AS CUR_WAL_CALL,
CT.TRMODDUR AS CUR_TRMODDUR,
CPA.RECORD_DATE AS CPA_RT,
CPS.RECORD_DATE AS CPS_RT,
CPSD.RECORD_DATE AS CPSD_RT,
DMB.RECORD_DATE AS DMB_RT,
CMC.RECORD_DATE AS CMC_RT,
CWC.RECORD_DATE AS CWC_RT,
CT.RECORD_DATE AS CT_RT,
CF.RECORD_DATE AS CF_RT

FROM
(SELECT
DEAL_BOND_NAME AS CUSIP,
DEAL_BOND_ID,
sum(trcurorigface * case tran_deal_type_name when 'Buy' then 1 when 'Sell' then -1 end) AS trcurorigface,
PORTFOLIO_NAME
FROM
VIEW_TRAN_DEAL_BOND
LEFT OUTER JOIN DEAL_BOND DB ON DB.ID = VIEW_TRAN_DEAL_BOND.DEAL_BOND_ID
WHERE
hedge = 0 OR hedge is null
GROUP BY
PORTFOLIO_NAME,
DEAL_BOND_NAME,
DEAL_BOND_ID) CTRP
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CF ON CF.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPA ON CPA.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPS ON CPS.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPSD ON CPSD.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY DMB ON DMB.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CMC ON CMC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CWC ON CWC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CT ON CT.ID = CTRP.DEAL_BOND_ID
WHERE
CPA.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH1
WHERE PX_ASK IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH1.DEAL_BOND_ID) and
CF.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH2
WHERE FACTOR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH2.DEAL_BOND_ID) and
CPS.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH3
WHERE PX_SPD IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH3.DEAL_BOND_ID) and
CPSD.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH4
WHERE PX_SPD_DT IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH4.DEAL_BOND_ID) and
DMB.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH5
WHERE DISC_MRGN_BID IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH5.DEAL_BOND_ID) and
CWC.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH6
WHERE WAL_CALL IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH6.DEAL_BOND_ID) and
CT.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH7
WHERE TRMODDUR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH7.DEAL_BOND_ID)

You should be able to create sub-queries that select the latest data for each of the seven fields by the key and join those seven via the key using a where clause in each of the sub-queries to filter out where your respective field is not NULL. In the sub-queries, be sure to order by the record date and only select top 1. An extremely simplified example follows:

select
t.Key,
t1.Field1,
t2.Field2
from
Table t
inner join
(
select top 1
t.Field1
from
Table t
where
t.Key = <Parameter Value>
and Field1 is not null
order by
t.RecordDate
) t1
on
t1.Key = t.Key
inner join
(
select top 1
t.Field2
from
Table t
where
t.Key = <Parameter Value>
and t.Field2 is not null
order by
t.RecordDate
) t2
on t2.Key = t.Key
where
t.Key = <Parameter Value>

I don't think your example is working because you're asking for a certain row where the Record_Date field is equal to (possibly) seven different values at the same time, which is impossible.

Copy table record as new record

Hi
I want to create a copy of an existing record in the same table, except with
a different primary key value of course.
This is pretty easy if you specify each in turn like:
INSERT INTO TableA
(
Col1
Col2
...
)
SELECT Col1, Col2, ...
FROM TableA
WHERE PK = @.RecordtoCopy
However, in my situation the database structure is evolving. Therefore, if
the fields in the TableA, above, are changed then the associated SQL script
is no longer valid.
Is there some way of doing the equivalent of:
INSERT INTO TableA
SELECT * FROM TableA
WHERE PK = @.RecordtoCopy
but leaving out the primary key field, which is an identity field.
That is, I don't want to have to specify the column names each time. I
guess I could use 'sycolumns' to construct a query or is there an easier way
?
What I've shown here is a very simplistic query. Mine are much more
complex, and I want to copy all fields from several records in tables formin
g
an hierarchical structure.
Cheers
TimHi Tim
I dont imagine a situation where you need to keep duplicate copy in the same
table.
Can you explain why you want to do that so to as provide some thought on tha
t.
Regards
R.D
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||This is for a cost estimating system where you want to create a new version
of an existing estimate. This would happen when you want to be on a new job
,
which is very similar to one that was previously costed and you want to use
the old one as a template for a new one.
I think I've solved it anyway. The code below is a function that creates an
insert clause as a string.
CREATE FUNCTION dbo.fn_CreateInsert (@.@.Table varchar(50), @.@.PKey
varchar(20)) RETURNS varchar(1000) AS
BEGIN
DECLARE @.InsertClause varchar(1000),
@.@.TableName varchar(255),
@.ColName varchar(255),
@.ColsCursor CURSOR
SET @.ColsCursor = CURSOR FAST_FORWARD
FOR
SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
INNER JOIN (sysobjects INNER JOIN sysusers ON sysobjects.uid =
sysusers.uid)
ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = @.@.Table
OPEN @.ColsCursor
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName, @.ColName
SET @.InsertClause = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.ColName NOT IN (@.@.PKey, 'DateCreated', 'DateModified')
BEGIN
IF @.InsertClause = ''
SET @.InsertClause = '(' + @.ColName
ELSE
SET @.InsertClause = @.InsertClause + ', ' + @.ColName
END
FETCH NEXT FROM @.ColsCursor
INTO @.@.TableName,@.ColName
END
CLOSE @.ColsCursor
DEALLOCATE @.ColsCursor
SET @.InsertClause = 'INSERT INTO ' + @.@.Table + ' ' + @.InsertClause + ')'
RETURN @.InsertClause
END
GO
"R.D" wrote:
> Hi Tim
> I dont imagine a situation where you need to keep duplicate copy in the sa
me
> table.
> Can you explain why you want to do that so to as provide some thought on t
hat.
> Regards
> R.D
> "Tim M" wrote:
>|||Tim, try,
-- DDL & sample data
create table dbo.t1
(
keycol int not null identity primary key,
col1 int,
col2 int,
col3 int
);
insert into dbo.t1 values(1, 2, 3);
go
-- usp_copyt1row proc
create proc usp_copyt1row
@.keyval as int
as
declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
set @.col_list = N'';
select @.col_list = @.col_list + ',[' + column_name + N']'
from information_schema.columns
where table_schema = N'dbo'
and table_name = N't1'
and column_name <> N'keycol';
set @.col_list = right(@.col_list, len(@.col_list) - 1);
set @.sql =
N'insert into dbo.t1('
+ @.col_list + N') select '
+ @.col_list + N' from dbo.t1 where keycol = @.keyval'
exec sp_executesql
@.sql, N'@.keyval as int', @.keyval;
go
-- test
exec usp_copyt1row 1;
exec usp_copyt1row 2;
select * from dbo.t1;
-- Output
keycol col1 col2 col3
-- -- -- --
1 1 2 3
2 1 2 3
3 1 2 3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Tim M" wrote:

> Hi
> I want to create a copy of an existing record in the same table, except wi
th
> a different primary key value of course.
> This is pretty easy if you specify each in turn like:
> INSERT INTO TableA
> (
> Col1
> Col2
> ...
> )
> SELECT Col1, Col2, ...
> FROM TableA
> WHERE PK = @.RecordtoCopy
> However, in my situation the database structure is evolving. Therefore, i
f
> the fields in the TableA, above, are changed then the associated SQL scrip
t
> is no longer valid.
> Is there some way of doing the equivalent of:
> INSERT INTO TableA
> SELECT * FROM TableA
> WHERE PK = @.RecordtoCopy
> but leaving out the primary key field, which is an identity field.
> That is, I don't want to have to specify the column names each time. I
> guess I could use 'sycolumns' to construct a query or is there an easier w
ay?
> What I've shown here is a very simplistic query. Mine are much more
> complex, and I want to copy all fields from several records in tables form
ing
> an hierarchical structure.
> Cheers
> Tim
>|||Thanks for that.
"Itzik Ben-Gan" wrote:
> Tim, try,
> -- DDL & sample data
> create table dbo.t1
> (
> keycol int not null identity primary key,
> col1 int,
> col2 int,
> col3 int
> );
> insert into dbo.t1 values(1, 2, 3);
> go
> -- usp_copyt1row proc
> create proc usp_copyt1row
> @.keyval as int
> as
> declare @.col_list as nvarchar(1000), @.sql as nvarchar(4000);
> set @.col_list = N'';
> select @.col_list = @.col_list + ',[' + column_name + N']'
> from information_schema.columns
> where table_schema = N'dbo'
> and table_name = N't1'
> and column_name <> N'keycol';
> set @.col_list = right(@.col_list, len(@.col_list) - 1);
> set @.sql =
> N'insert into dbo.t1('
> + @.col_list + N') select '
> + @.col_list + N' from dbo.t1 where keycol = @.keyval'
> exec sp_executesql
> @.sql, N'@.keyval as int', @.keyval;
> go
> -- test
> exec usp_copyt1row 1;
> exec usp_copyt1row 2;
> select * from dbo.t1;
> -- Output
> keycol col1 col2 col3
> -- -- -- --
> 1 1 2 3
> 2 1 2 3
> 3 1 2 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim M" wrote:
>

Copy Table

How can I copy the table structure only (no data) keeping foreing key, indexes ...etc. I used DTS and it copied the table but not the relationship to other tables of index information
Thanks
Use Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks

Copy Table

How can I copy the table structure only (no data) keeping foreing key, indexes ...etc. I used DTS and it copied the table but not the relationship to other tables of index information
ThanksUse Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks

Copy Table

How can I copy the table structure only (no data) keeping foreing key, index
es ...etc. I used DTS and it copied the table but not the relationship to o
ther tables of index information
ThanksUse Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks