Getting Identity Property in Another DB

[rickj65]

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

 

[tc3596]

CREATE PROCEDURE test

@PassDatabase varchar(20)

AS

exec('use ' + @PassDatabase )
select * from table1_sql
GO

why won't this work

 

[rickj65]

why won't this work"...

Because I didn't think of it. It didn't even occur to me that I should've used dynamic SQL to set the database connection.

Thanks for the suggestion...it works like a charm!

Regards,

Rick

 

[vongrunt]

Are you sure?

Database gets changed only within exec().

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba