adding identity column dynamically

[Sam]

Hi,
In my stored procedure I'm doing a SELECT on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id on
this table, so I was wondering if it was possible to add it dynamically
in my SELECT, so that I would assign a unique id to each record
returned by my SELECT?

Thanks for your support.

[Robert Klemme]

Sam wrote:

Hi,
In my stored procedure I'm doing a SELECT on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id on
this table, so I was wondering if it was possible to add it
dynamically in my SELECT, so that I would assign a unique id to each
record returned by my SELECT?

Where do you need that for? Maybe this helps:

http://support.microsoft.com/default...;EN-US;q186133

robert

[Erland Sommarskog]

Sam (sa**************@voila.fr) writes:

In my stored procedure I'm doing a SELECT on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id on
this table, so I was wondering if it was possible to add it dynamically
in my SELECT, so that I would assign a unique id to each record
returned by my SELECT?

There should be no need for this. There is a unique key in
(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME).

To get an IDENTITY column you could bounce over a temp table with:

SELECT ident = IDENTITY(int, 1, 1), CONSTRAINT_CATATLOG, ...
INTO #temp
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE ...

SELECT * FROM #temp

But, as I said there is no need to do this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sam]

Thanks. That's what I needed.
I don't want to use the triplet as an id, i want a unique number, it's
easier to handle on the client code side.