TSQL: Create a view that accesses multiple databases

by

Yes you can – the t-sql syntax is the same as within any other cross database call (within a stored procedure for example).

To reference your tables in the second database you simply need:

[DatabaseName].[Schema].[TableName]

So you would end up with something like

CREATE VIEW [dbo].[YourView]
as
select 
a.ID, 
a.SomeInfo, 
b.SomeOtherInfo
from TableInA a
join DatabaseB.dbo.TableInB b
on -- your join logic goes here

Note that this will only work on the same server – if your databases are on different servers them you will need to create a linked server.

More Related Contents:

  1. Create a view with ORDER BY clause
  2. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  3. How to check if a database exists in SQL Server?
  4. How do I create a unique constraint that also allows nulls?
  5. How do I get list of all tables in a database using TSQL?
  6. Get first day of week in SQL Server
  7. What is the significance of 1/1/1753 in SQL Server?
  8. Escape a string in SQL Server so that it is safe to use in LIKE expression
  9. SQL time difference between two dates result in hh:mm:ss
  10. Dynamic SQL (passing table name as parameter)
  11. SQL Server: Difference between PARTITION BY and GROUP BY
  12. How do you truncate all tables in a database using TSQL?
  13. SQL Server: How to select all days in a date range even if no data exists for some days
  14. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  15. TSQL: How to convert local time to UTC? (SQL Server 2008)
  16. T-SQL Dynamic SQL and Temp Tables
  17. How to determine total number of open/active connections in ms sql server 2005
  18. Find non-ASCII characters in varchar columns using SQL Server
  19. TABLOCK vs TABLOCKX
  20. How to create a yes/no boolean field in SQL server?
  21. Best way to store time (hh:mm) in a database
  22. How do I select last 5 rows in a table without sorting?
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Include missing months in Group By query
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. @@ERROR and/or TRY – CATCH
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment