I am migrating data from 1 database to another with a different schema altogether and need to designate identity columns in the original. If I am in the new database and issue the following from a stored procedure where northwind is the original..
select o.name , c.name AS [idcolumn]
from northwind..sysobjects o join northwind..syscolumns c
on o.id = c.id
and COLUMNPROPERTY(o.id, c.name, 'IsIdentity') = 1
I get nothing, because apparently the COLUMNPROPERTY is bound to the local database. Is there a way to "tell" the system to use the northwind COLUMNPROPERTY?
I know I can just issue a USE NORTHWIND before the query and then switch back if I use a standard script, but I would like to keep it in a procedure file.
TIA