Create view from data accross multiple databases

Question

After some searching around i couldn't find a good answer that covers my issue. I am working on the consolidation of around 100 databases. The structure is the same and they are all on the same server. All the databases have a table with login information.

We have created a core database with all the connection information from the other databases. Now we need to create a view in the core database that contains all the login credentials from all the databases. This means we need to use a loop to go through all the databases and select the user name and password.

Any ideas or suggestions are welcome

Answer 1

One possible solution is to create a stored procedure

DECLARE @sql varchar(max), @Database1 varchar(300)
set @Database1 = 'tempdb'
SET @sql=' 
USE '+@Database1+'; 
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''test_view'')
BEGIN
DROP VIEW test_view
PRINT ''VIEW EXISTS''
END'
PRINT @sql
EXEC(@sql)    


declare @sql1 varchar(max)

// Modify below query as per your requirement its just for an idea

select @sql1 = IsNull(@sql1 + 'union all ','') +
              'select * from ' + name + '.dbo.tblUser'
from   sys.databases
where  name like 'DbNamePrefix%'

set @sql1 = 'create view dbo.YourView as ' + @sql1
exec (@sql1)

Make a database job and schedule it as per your requirement.