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


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.


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  


Could you instead use OPENQUERY (see here)?


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.


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »