How to create a dynamic view across databases in same server.


  • 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


    Wednesday, October 30, 2013 6:37 PM

All replies

  • There's no such thing as a "dynamic view".  Just use dynamic SQL, or create a stored procedure to re-recreate the view every time you add or remove a database.



    Wednesday, October 30, 2013 6:43 PM
  • Could you provide us the error message which you getting while executing the query?

    Wednesday, October 30, 2013 11:22 PM
  • Rather you can use temporary tables to store the required data from other servers and then act on them.


    Wednesday, October 30, 2013 11:44 PM
  • You can also try CTE to achieve the same results.

    Thursday, October 31, 2013 3:00 AM
  • You can do something like shown below. I would question entire approach though. From both, supportability and performance standpoints. Also, use union all rather than union unless you need distinct set of data.

    -- Run in context of one DB
    	@ColNames nvarchar(max)
    	,@Sql nvarchar(max)
    	,@TableName sysname = N'<TableNameHere>'
    ;with Cols(ColNames)
    	select '[' as [text()], c.Name as [text()], '],' as [text()]
    	from sys.tables t join sys.columns c on t.object_id = c.object_id
    	where t.object_id = object_id(@TableName)
    	order by c.column_id
    	for xml path('')
    	@ColNames = substring(ColNames,1,len(ColNames) - 1)
    from Cols;
    if @ColNames is null
    	raiserror('Column list is empty. Perhaps invalid db/tablename?',16,1)
    else begin
    	;with DBs(DBView)
    			'select ' + @ColNames + ' from [' as [text()]
    			,d.Name as [text()]
    			,'].' + @TableName + ' union all ' as [text()]
    		from sys.databases d
    		where d.database_id >= 5 and d.database_id < 32767 -- in ()
    		order by d.database_id
    		for xml path('')
    		@Sql = N'create view dbo.MyView(' + @ColNames + N') as ' + substring(DBView,1,len(DBView) - 10)
    	from DBs;
    	select @Sql for xml path('')
    	--exec sp_executesql @Sql

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog:

    Thursday, October 31, 2013 4:30 AM
  • Hey Shamsuddeen,

    Try this:

    CREATE View YourViewName


    SELECT A, B, C FROM [Database1].[dbo].[table]


    SELECT A, B, C FROM [Database2].[dbo].[table]


    SELECT A, B, C FROM [Database3].[dbo].[table]

    I believe this is simplest solution that you also might have thought. What is the challenge that you are facing using this?

    Thursday, October 31, 2013 6:22 AM
  • Thanks all for your input.

    I was able to crack the same using cursor and using dynamic query.


    Thursday, October 31, 2013 5:31 PM
  • I am moving it to T-SQL.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, November 01, 2013 10:24 PM