> 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.?

You can specify the desired database context with a USE statement
immediately before the SELECT to set the database context.

To return data from different databases in the same query, you'll need to
use the technique Erland suggested and use a UNION ALL to concatenate
results from different databases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<newtophp2000@yahoo.com> wrote in message
news:1108137110.964647.268740@o13g2000cwo.googlegr oups.com...
> Dan Guzman wrote:
>> You might try specifying the table schema in the OBJECT_ID function
> to avoid
>> ambiguity. Also, consider quoting the identifiers:
>>
>> SELECT COLUMN_NAME
>> FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_SCHEMA = 'user_a' AND
>> TABLE_NAME = 'tab_a' AND
>> COLUMNPROPERTY(
>> OBJECT_ID(
>> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
>> COLUMN_NAME, 'IsIdentity') = 1
>
> Hi Dan,
>
> 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.?
>
> Thanks!
>
>
>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>