locked
How to create dynamic view across different databases RRS feed

  • Question

  • Hi,

    I have got a requirement to create a dynamic view which union all the data from different dadtabases (same table, we  are pulling the data).

    for ex: i have a table "A" in say 5 databases. i need create a dynamic view which union all the data from all the database.

    select * from A (from (database1) unnion select * from A (from (database2)..... etc.

    tried to create through dynamic SP by first selecting all the databases. but throws error saying that create view must be the first statement. Can anybody please help

    alter proc test

    as begin

    declare @query as nvarchar(max)

    set @query='declare #table as table (Name varchar(10),dept varchar(10),mark int)

    insert into #table
    select * from test1 go  create view b as select * from @table'print @query
    exec sp_executesql @query
    select * from b

    end

    Wednesday, October 30, 2013 2:28 PM

Answers

All replies