title: Getting metadata from another database in an sproc in SQL Server?
body: <p>How do I create a stored procedure that exists in one database but runs the below code against another (any) database?</p>
  
  <pre><code>SET @sql1 = N'INSERT INTO #Tables SELECT'
            + N' t.TABLE_NAME as TableName'
            + N',t.TABLE_SCHEMA as SchemaName'
            + N',(SELECT OBJECTPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + ''.'' + t.TABLE_NAME),''TableHasIdentity'')) '
            + N'FROM ' + QUOTENAME(@TargetDBName) 
            + N'.INFORMATION_SCHEMA.TABLES t'
  IF @Verbose = 1
      PRINT @sql1
  EXEC(@sql1)
  </code></pre>
  
  <p>I get TABLE_NAME and SCHEMA_NAME successfully, but the main issue is that OBJECTPROPERTY() runs in the context of the stored procedure's database, not in the context of @TargetDBName. So, OBJECTPROPERTY() will always return null, unless @TargetDBName is the same as the database the sproc is in.</p>
  
  <p>I am currently using SQL Server 2008.</p>