Nsryjdtyk

I need to do something like:

SELECT    data, hour, user, 

FROM         [dbx].dbo._datamodel AS db0

UNION

SELECT    data, hour, user, 

FROM         [dby].dbo._datamodel AS db1

UNION

SELECT    data, hour, user, 

FROM         [dbz].dbo._datamodel AS db02

UNION

...

We have to do it for a lot of DDBB and we are looking the way to do it automatically for each DB with a condition.
We don't want to add the UNION and select for each DB every time.

Any one have an advice or idea how to do it?

I think you are looking for DynamicSQL

DECLARE @SQL NVARCHAR(MAX) = N'SELECT data, hour, user FROM ';



SELECT @SQL = @SQL + [name] + '.dbo._datamodel UNION SELECT * FROM '

FROM master.dbo.sysdatabases 

WHERE dbid > 4 ; --ReportServer and ReportServerTempDB  with dbid 5 and 6

SET @SQL = LEFT(@SQL, LEN(@SQL) - 21);

SELECT @SQL;

--EXECUTE sp_executesql @SQL;

I would simply create a view in a standard database that has the union:

create view v_datamodel as

    select . . .

    union

    . . .;

(Side note: If you really just want union all, then use it. union incurs lots of extra overhead.)

If your set of databases is fixed, then you can create the view once -- set it and forget it.

If databases are being added and removed, then you have a process for doing so. This process can update the view, perhaps by using dynamic SQL as suggested in another answer. Or, you might have the list of appropriate databases somewhere other than sysdatabases.

If the database are created "ad hoc", then you could use DDL triggers. I'm not sure if you can define these on create database, but within each database, you could check for the creation of the "data model" table.

I was trying and still don't working.
I've been trying with :

Declare @sql AS VARCHAR(8000)
SET @sql = ' IF ''?'' like ''dat%''
EXECUTE ('' USE [?]
Select process, processVers
from dbo._datamodel
'')'
EXEC sp_MSforeachdb @sql

It works for the SQL query, but i doesn't for the view. :(
Any advice?