Post reply

Create context-independent View (behaviour like INFORMATION_SCHEMA ones)

  • April 21, 2011 at 3:48 am

    #100373

    I have a view on a database very useful, so I'd like to use it on another databases. The first idea was to create the same view everywhere I need it, but I'm asking for a way to create it once and have it available everywhere.

    As the view reads system tables, it's not enough to call it from anywhere else as in

    USE DB2

    GO

    Select * From DB1.dbo.MyView

    GO

    because I want the view (or SP) to read de system tables of the current context (DB2).

    That is, I'd like my view to have the same behavior of the INFORMATION_SCHEMA view, because they can be invoked everywhere and get the context from the execution context.

    TIA,

    Diego, BCN, Spain

  • April 21, 2011 at 8:00 am

    #1314532

    How about creating a separate "system" database and put a stored procedure in there that would return the data you need based on the database parameter passed in:

    Use NewSystemDb;

    CREATE PROCEDURE dbo.db_data(@dbname varchar(128))

    AS

    BEGIN

    DECLARE @sql varchar(100)

    SET @sql = 'SELECT * FROM ' + @dbname + '.<@dbname_table>'

    EXEC(@Sql)

    END

    Then you can call the stored procedure from any database:

    Use DB1;

    EXEC NewSystemDb.dbo.db_data 'DB1'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply