Hello
I am creating a view which references a number tables in another database. For this, I would usually add "USE database name" and then "GO".
I am however getting an error message which says "Incorrect syntax near the keyword 'USE'. a USE database statement is not allowed in a procedure, function or trigger.
Is the only answer for this to create all the tables I am referencing as views?
Thanks in advance! 
I have found the solution by referencing the database in the table name...d'oh!
Yup, that works 
SELECT A.Col1, B.Col2, ...
FROM DatabaseA.dbo.TableA AS A
JOIN DatabaseB.dbo.TableB AS B
ON B.ID = A.ID
...
however ... databases tend to move around / get split / get renamed / move to different servers, and you might have Production, Test and Development versions - perhaps sometimes you need to connect DEV of DatabaseA to Production of DatabaseB and so on ...
I prefer to use SYNONYMS for that sort of thing (both for 3-part-naming of tables in other databases, and also 4-part naming for things on different servers).
I create a Schema for each "external" database
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'DatabaseB')
BEGIN
EXEC( 'CREATE SCHEMA DatabaseB' )
END
and then I create a SYNONYM for each table in DatabaseB that I want to access (from the current database).
IF (SELECT OBJECT_ID('DatabaseB.Table1')) IS NOT NULL
DROP SYNONYM DatabaseB.Table1
CREATE SYNONYM DatabaseB.Table1
FOR DatabaseB.dbo.Table1
then (assuming that the current database is DatabaseA) the above query (repeated here):
SELECT A.Col1, B.Col2, ...
FROM DatabaseA.dbo.TableA AS A
JOIN DatabaseB.dbo.TableB AS B
ON B.ID = A.ID
...
becomes this:
SELECT A.Col1, B.Col2, ...
FROM dbo.TableA AS A
JOIN DatabaseB.TableB AS B
ON B.ID = A.ID
...
If DatabaseB changes name or I want to access the TEST database version instead of the DEV database version, or DatabseB moves to a new server, or TableB in DatabaseB moves to DatabaseX on ServerY then i can just change the SYNONYM definition and I don't have to change a single line of code.
Long story short: avoid hardcoding Database and Server names in your SQL queries.
I support the use of synonyms or views (more flexible, which has both good points and bad points), but I'd strongly urge you not to use a different schema name, in particular one that matches the remote database name. My reasons are:
Keep in mind, too, that you don't need to use the actual table name as the synonym name. That is, synonym name "abc" can refer to table "diffdb.dbo.def".
Yeah, that's a valid point. In principle I use a schema name that represents the target database, rather than its actual name. They are often the same in practice though - 3rd party stuff here seems to be given a database name matching the product, or supplier company, name e.g. "Acme" - and everyone refers to it as "Acme" ...
... of course when Acme is bought out by MegaWidgets we hit a snag! - same legacy-issue that we have every day about Short Codes used for Names when company / personal names then change, and you have to have been in the company for a million years to know that the code for MegaWidgets is "ACME01" 
That's more worrying and I didn't know that. So I'be better off prefixing (say) all the Synonym tables names with "ACME_" and stuffing the Synonyms in "dbo" schema rather than creating their own "Acme" schema?
(Suits me to keep the table names the same as the Remote, 'coz I've only ever experienced table names in 3rd party APPs/DBs that make sense! but I am aware that some vendors call their tables X12345 or whatever ... I figure I'd want a VIEW in front of that to "improve" both the table name, but also presumably the similarly stupidly named column names too.
I guess you could prefix the synonym with a company name, if you're determined to have a name in there.
(Suits me to keep the table names the same
Yes, clearly not a requirement. Might just be me, but the only time this comes up for us is when we need to reference data in another database in lieu of having that data in our own. For us its a strictly readonly channel. If I was creating an APP that had, say, both Accounts and MRP functionality, each half in its own database, then I'd be able to treat it as "one" wrt table naming. So because of my own circumstances I'm probably not seeing the bigger picture ... our usage is to reference some 3rd party database, often "somewhat useless" IMHO !! and I want to segregate their tables in a way that does not "intrude" into my application [e.g. when someone looks at the Table List in our DB etc.]. I've been stuffing them into their own Schema, but for me an "ACME_" tablename-prefix would organise them similarly.
Generally I would import relevant data from the 3rd party database into tables designed to suit my application, rather than remote-reference them, because often the 3rd party database don't allow creation of additional indexes and so on, so any direct access I made might perform like a dog ... or cause blocking / whatever.
But of course my local copy is only as good as the time that it was last freshened up, so for some displays (reports, whatever) we sometimes need to include "current data" and then query the 3rd party DB direct.
All this rather philosophical; the discussion is very useful to me, but I apologise to O/P for derailing the original question.
Just in case my previous description lacked clarity, in saying I wanted to "keep table names the same" that was so that when I need to work in the remote database there is continuity of naming between the local synonym name in my DB and the table name in the 3rd party database, so that everything is familiar to me (doesn't have to be that way of course). Using either a specific Schema for my Synonyms, or a prefix (e.g. "ACME_") for synonym name, seems to solve that for me (I should have said "Synonym name" in my earlier post, rather than "table name", sorry about that - I was thinking of my Schema / Synonym as SchemaNameMatchingOtherDBName.TableName and fooled myself that it was a TableName - Doh!)