(newtophp2000@yahoo.com) writes:
> As I noted before, this works; however, it seems that it doesn't do the
> right thing if the databases are different.
>
> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?

SELECT *
FROM db..sysobjects o
JOIN db..syscolumns c ON o.id = c.id
JOIN db..sysusers u ON o.uid = u.uid
WHERE o.name = @tbl
AND c.name = @col
AND u.name = @user
AND c.status & 0x80 <> 0

will return a row if the column is an identity column.

When I wrote this query, I assumed that I was on undocumented ground,
but this value is actually documented for syscolumns.status, and thus
permissible to use. The code should work in SQL 2005 as well. (Although
SQL 2005 also offer new catalog views which are better for the task.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp