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

score 2 · Accepted Answer · answered Jul 06 '12 at 06:26

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.

score 1 · Answer 2 · answered Jul 06 '12 at 04:51

1

To reference to your tables in the second database use this: [DBName].[dbo].[TableName] e.g.

CREATE VIEW [dbo].[ViewName]
as
select 
a.ID, 
a.Name, 
b.Address
from TableA a
join SecondDBName.dbo.Table b
on ... ---Remaining code here...

NOTE: This will work only on the same server - if your databases are on different servers then you will need to create a linked server.

answered Jul 06 '12 at 04:51

Himanshu

31,810
31
111
133

correct but the issue is that the databases are added and removed sometimes. I do not want to adjust the view each time manually. So i will need some for loop that goes over each db and select the data. – Jan Van Looveren Jul 06 '12 at 05:17
@JanVanLooveren If database is removed, how can you fetch data from specific table (removed database's table)? – Himanshu Jul 06 '12 at 05:22
if the database is removed, the credentials are removed from the view as well. Therefor i am searching to create a view that can be dynamic. – Jan Van Looveren Jul 06 '12 at 05:52

score 0 · Answer 3 · answered Jul 06 '12 at 15:10

0

Take a look at this. Can this be one of the answers to your question? http://blog.springsource.org/2007/01/23/dynamic-datasource-routing/

answered Jul 06 '12 at 15:10

ancora imparo

82
2
10

Create view from data accross multiple databases

3 Answers3