fn_listextendedproperty function using multiple databases

[AccessSQLUser]

I am trying to compare the values of the extended properties of stored procedures from 2 different databases. Is there any way to do this in a stored procedure? I was thinking of creating 2 temporary tables that will include the extended properties and the values of each table but the problem is that I can't switch databases since the keyword USE does not work in a stored procedure and I can't use the syntax Database.OwnerName.ObjectName since the function doesn't belong to any particular database. Any ideas?

 

[tlbroadbent]

Although a USE statement does not work in a stored procedure, you can create a dynamic SQL statement that includes a USE statement and execute it within a stored procedure.

Example:

Declare @sql varchar(255)
Select @sql="USE MyDB Insert #temptbl1 exec proc"
Exec (@sql)

The USE is executed only in the context of the statements contained in @SQL. When the execution completes the connection will be in the original datasbase.

 

[AccessSQLUser]

Thanks. That worked. Now I need to do a select statement using the temp tables but it seems that after I execute the SQL statement, the temp tables are already deleted.

 

[tlbroadbent]

I apologize. You'll need to create the temp tables before executing the Inserts.

Create Table #temptbl1 (col1 int, col2 char(4),...)
Create Table #temptbl1 (colA varchar(32), colB char(12),...)

Declare @sql varchar(255)
Select @sql="USE MyDB1 Insert #temptbl1 exec proc"
Exec (@sql)
Select @sql="USE MyDB2 Insert #temptbl2 exec proc"
Exec (@sql)

 

[AccessSQLUser]

Thank you! It worked!