You can also try CTE to achieve the same results.
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 declare @ColNames nvarchar(max) ,@Sql nvarchar(max) ,@TableName sysname = N'<TableNameHere>' ;with Cols(ColNames) as ( 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('') ) select @ColNames = substring(ColNames,1,len(ColNames) - 1) from Cols; if @ColNames is null begin raiserror('Column list is empty. Perhaps invalid db/tablename?',16,1) end else begin ;with DBs(DBView) as ( select '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 -- d.name in () order by d.database_id for xml path('') ) select @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 end
Dmitri V. Korotkevitch (MVP, MCM, MCPD)
My blog: http://aboutsqlserver.com
- Edited by Dmitri KorotkevitchMVP Thursday, October 31, 2013 4:36 AM
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?