Tutorial :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.


Solution:1

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  


Solution:2

Could you instead use OPENQUERY (see here)?


Solution: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.


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