Getting Primary Key column names for all tables

[Sumit]

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.

[Nishant Grover]

The following query gives comma separated PK columns for a table. You can possibly execute this query for all the tables:

Cheers,
Nishant

DECLARE @table_name VARCHAR(30),
@ind_id INT,
@this_key VARCHAR(255),
@keys VARCHAR(255),
@counter INT
SELECT @table_name = <&#39;your table name goes in here&#39;>,
@ind_id = 1,
@counter = 1
WHILE @counter <= 16
BEGIN
SELECT @this_key = INDEX_COL (@table_name, @ind_id, @counter)
IF @this_key IS NULL
BREAK
IF @counter = 1
SELECT @keys = @this_key
ELSE
SELECT @keys = @keys + &#39;,&#39; + @this_key
SELECT @counter = @counter + 1
END
SELECT @keys



------------
Sumit at 9/9/99 10:50:42 AM

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can&#39;t get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.

[Sumit]

Thanks for your reply. I was able to figure out how to get the primary Key column name but now i am stuck up on the next step. Following is the code i am using :-


DECLARE @table_name CHAR(18)
DECLARE @col_name CHAR(50)
DECLARE @last_key INTEGER
DECLARE @string CHAR(50)


DECLARE seq_cursor CURSOR FOR
SELECT ref_id FROM seq_num_keys
OPEN seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
WHILE (@@FETCH_STATUS <> -1)
BEGIN

1) SELECT @col_name = c.name
FROM sysobjects so, syscolumns c, sysconstraints co, sysobjects t
WHERE so.type = &#34;K&#34;
AND so.id = co.constid
AND co.id = c.id
AND c.colid = 1
AND co.id = t.id
AND t.name = @table_name



2) SELECT @String = &#39;SELECT @last_key = max(&#39; + @col_name + &#39 FROM &#39; + @table_name
EXEC sp_sqlexec @String
SELECT @string

3) UPDATE seq_num_keys
SET last_value = @last_key
WHERE CURRENT OF seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
END

DEALLOCATE seq_cursor
GO

There seems to be some problem with query # 2 because when i say select @string it gives me &#39;SELECT @last_key = max(<Actual Table Column Name> &#39; and then gives an error because this statement is not complete and i am trying to execute it. What could be wront with query # 2 ???


------------
Nishant Grover at 9/9/99 11:32:51 AM

The following query gives comma separated PK columns for a table. You can possibly execute this query for all the tables:

Cheers,
Nishant

DECLARE @table_name VARCHAR(30),
@ind_id INT,
@this_key VARCHAR(255),
@keys VARCHAR(255),
@counter INT
SELECT @table_name = <&#39;your table name goes in here&#39;>,
@ind_id = 1,
@counter = 1
WHILE @counter <= 16
BEGIN
SELECT @this_key = INDEX_COL (@table_name, @ind_id, @counter)
IF @this_key IS NULL
BREAK
IF @counter = 1
SELECT @keys = @this_key
ELSE
SELECT @keys = @keys + &#39;,&#39; + @this_key
SELECT @counter = @counter + 1
END
SELECT @keys



------------
Sumit at 9/9/99 10:50:42 AM

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can&#39;t get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.

How can i be so dumb !!!

I got it right finally. Actually i had declared @col_name as char(50) and @string also as char(50). So i juct changed them to varchar(50) & varchar(100). Now it&#39;s working fine !

Thanks anyway.


------------
Sumit at 9/9/99 11:59:20 AM

Thanks for your reply. I was able to figure out how to get the primary Key column name but now i am stuck up on the next step. Following is the code i am using :-


DECLARE @table_name CHAR(18)
DECLARE @col_name CHAR(50)
DECLARE @last_key INTEGER
DECLARE @string CHAR(50)


DECLARE seq_cursor CURSOR FOR
SELECT ref_id FROM seq_num_keys
OPEN seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
WHILE (@@FETCH_STATUS <> -1)
BEGIN

1) SELECT @col_name = c.name
FROM sysobjects so, syscolumns c, sysconstraints co, sysobjects t
WHERE so.type = &#34;K&#34;
AND so.id = co.constid
AND co.id = c.id
AND c.colid = 1
AND co.id = t.id
AND t.name = @table_name



2) SELECT @String = &#39;SELECT @last_key = max(&#39; + @col_name + &#39 FROM &#39; + @table_name
EXEC sp_sqlexec @String
SELECT @string

3) UPDATE seq_num_keys
SET last_value = @last_key
WHERE CURRENT OF seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
END

DEALLOCATE seq_cursor
GO

There seems to be some problem with query # 2 because when i say select @string it gives me &#39;SELECT @last_key = max(<Actual Table Column Name> &#39; and then gives an error because this statement is not complete and i am trying to execute it. What could be wront with query # 2 ???


------------
Nishant Grover at 9/9/99 11:32:51 AM

The following query gives comma separated PK columns for a table. You can possibly execute this query for all the tables:

Cheers,
Nishant

DECLARE @table_name VARCHAR(30),
@ind_id INT,
@this_key VARCHAR(255),
@keys VARCHAR(255),
@counter INT
SELECT @table_name = <&#39;your table name goes in here&#39;>,
@ind_id = 1,
@counter = 1
WHILE @counter <= 16
BEGIN
SELECT @this_key = INDEX_COL (@table_name, @ind_id, @counter)
IF @this_key IS NULL
BREAK
IF @counter = 1
SELECT @keys = @this_key
ELSE
SELECT @keys = @keys + &#39;,&#39; + @this_key
SELECT @counter = @counter + 1
END
SELECT @keys



------------
Sumit at 9/9/99 10:50:42 AM

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can&#39;t get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.

[Sumit]

Oh no, now it has given me another error !!!

&#39;Must declare variable @last_Keys&#39;.

I have already declared the variable @last_Key then why is it asking me to declare it again !



------------
at 9/9/99 12:07:24 PM

How can i be so dumb !!!

I got it right finally. Actually i had declared @col_name as char(50) and @string also as char(50). So i juct changed them to varchar(50) & varchar(100). Now it&#39;s working fine !

Thanks anyway.


------------
Sumit at 9/9/99 11:59:20 AM

Thanks for your reply. I was able to figure out how to get the primary Key column name but now i am stuck up on the next step. Following is the code i am using :-


DECLARE @table_name CHAR(18)
DECLARE @col_name CHAR(50)
DECLARE @last_key INTEGER
DECLARE @string CHAR(50)


DECLARE seq_cursor CURSOR FOR
SELECT ref_id FROM seq_num_keys
OPEN seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
WHILE (@@FETCH_STATUS <> -1)
BEGIN

1) SELECT @col_name = c.name
FROM sysobjects so, syscolumns c, sysconstraints co, sysobjects t
WHERE so.type = &#34;K&#34;
AND so.id = co.constid
AND co.id = c.id
AND c.colid = 1
AND co.id = t.id
AND t.name = @table_name



2) SELECT @String = &#39;SELECT @last_key = max(&#39; + @col_name + &#39 FROM &#39; + @table_name
EXEC sp_sqlexec @String
SELECT @string

3) UPDATE seq_num_keys
SET last_value = @last_key
WHERE CURRENT OF seq_cursor
FETCH NEXT FROM seq_cursor INTO @table_name
END

DEALLOCATE seq_cursor
GO

There seems to be some problem with query # 2 because when i say select @string it gives me &#39;SELECT @last_key = max(<Actual Table Column Name> &#39; and then gives an error because this statement is not complete and i am trying to execute it. What could be wront with query # 2 ???


------------
Nishant Grover at 9/9/99 11:32:51 AM

The following query gives comma separated PK columns for a table. You can possibly execute this query for all the tables:

Cheers,
Nishant

DECLARE @table_name VARCHAR(30),
@ind_id INT,
@this_key VARCHAR(255),
@keys VARCHAR(255),
@counter INT
SELECT @table_name = <&#39;your table name goes in here&#39;>,
@ind_id = 1,
@counter = 1
WHILE @counter <= 16
BEGIN
SELECT @this_key = INDEX_COL (@table_name, @ind_id, @counter)
IF @this_key IS NULL
BREAK
IF @counter = 1
SELECT @keys = @this_key
ELSE
SELECT @keys = @keys + &#39;,&#39; + @this_key
SELECT @counter = @counter + 1
END
SELECT @keys



------------
Sumit at 9/9/99 10:50:42 AM

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can&#39;t get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.