Getting metadata from another database in an sproc in SQL Server?

Question

How do I create a stored procedure that exists in one database but runs the below code against another (any) database?

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)

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.

I am currently using SQL Server 2008.

Answer 1

Could you instead use OPENQUERY (see here)?

Answer 2

Query the sys views directly like this

SELECT  Tbl.name AS TableName, sch.name AS SchemaName,
HasIdentity = CASE WHEN EXISTS (SELECT * FROM your_target_db.sys.columns AS cols WHERE Tbl.object_id = cols.object_id and is_identity = 1) THEN 1 ELSE 0 END
 FROM your_target_db.sys.tables AS Tbl INNER JOIN  your_target_db.sys.schemas AS SCH ON Tbl.schema_id = Sch.schema_id

Answer 3

Have you considered using IDENT_SEED in your dynamic query?

This will return the seed value of a table's identity column or NULL if one doesn't exist.

Example:

USE master
GO

CREATE DATABASE Test
GO

USE Test
GO

CREATE TABLE Test1 (ColA INT IDENTITY(100,1))
CREATE TABLE Test2 (ColA INT)
GO

USE master
GO

DECLARE @TargetDBName NVARCHAR(MAX), @sql1 NVARCHAR(MAX)

SET @TargetDBName = 'Test'

SET @sql1 = N'SELECT' 
          + N' t.TABLE_NAME as TableName' 
          + N',t.TABLE_SCHEMA as SchemaName' 
          + N',(SELECT CASE WHEN IDENT_SEED(''' 
            + QUOTENAME(@TargetDBName) + '.'' 
            + t.TABLE_SCHEMA + ''.'' 
            + t.TABLE_NAME) IS NOT NULL THEN 1 ELSE 0 END) as HasIdentity ' 
          + N'FROM ' + QUOTENAME(@TargetDBName)  
          + N'.INFORMATION_SCHEMA.TABLES t'  

EXEC(@sql1) 
GO

DROP DATABASE Test
GO

The results:

TableName  SchemaName  HasIdentity
---------- ----------- -----------
Test1      dbo         1
Test2      dbo         0

One caveat you may need to consider:

Returns NULL on error or if a caller does not have permission to view the object.