I'm writing a proc in one database that will find all tables in a different database that have an Identity column (and then ultimately reseed the Identity).
I can determine if a table has an Identity column by running the following within the current db...
select objectproperty(object_id('mydb..mytable'), 'TableHasIdentity')
The result = 1
However, the problem I'm having is that I need to execute this so it determines the tables with Identity columns in a different db. If I try to run the query from a different database connection in QA, it returns NULL.
Ultimately, I want to execute this in a stored procedure where I pass the database name as a parameter and use dynamical SQL to create the cursor of table names, and then reseed those tables by looping through the cursor. Unfortunately, I can't issue the "USE mydb" statement in the stored proc which would solve the problem.
Any ideas or other workarounds?
Thanks,
Rick
I can determine if a table has an Identity column by running the following within the current db...
select objectproperty(object_id('mydb..mytable'), 'TableHasIdentity')
The result = 1
However, the problem I'm having is that I need to execute this so it determines the tables with Identity columns in a different db. If I try to run the query from a different database connection in QA, it returns NULL.
Ultimately, I want to execute this in a stored procedure where I pass the database name as a parameter and use dynamical SQL to create the cursor of table names, and then reseed those tables by looping through the cursor. Unfortunately, I can't issue the "USE mydb" statement in the stored proc which would solve the problem.
Any ideas or other workarounds?
Thanks,
Rick