How to shorten or alias database names in your query?

Question

I have ten to twenty databases in my server. Everytime i need to query i want to put something like this

Select *  from otp-devices.dbo.deviceid 
select module from mr1ghp/security.dbo.alarmmodlink

Our database names are long . Is it possible to shorten or alias database name in query otp-devices mr1ghp/security

I don't understand your question. First, `otp-devices.dbo.deviceid` is not a valid name for a database. It comes close to a table reference (but the hyphen is incorrect). What is your issue? Lots of databases or lots of tables? — Gordon Linoff, Oct 14 '17 at 13:05
There is no straight forward way to do this. [see this answer](https://stackoverflow.com/questions/21722066/how-to-create-an-alias-of-database-in-sql-server) — CodingYoshi, Oct 14 '17 at 13:20
Do you need three part names anyway? Unless you are performing cross database queries just `USE YourDb` and use two part names. — Martin Smith, Oct 14 '17 at 14:46
i mostly use cross queries and often my queries end up as long — Bodhi, Oct 14 '17 at 19:23

score 2 · Answer 1 · answered Oct 14 '17 at 13:21

2

You can make an alias with the keyword AS like this :

SELECT * FROM `otp-devices.dbo.deviceid ` AS otp

answered Oct 14 '17 at 13:21

Arrabidas92

1,113
1
9
20

This is true for one query. but I have seen a way to do it from the database level itself. that is we can use that name for our database name in anywhere in our sql server. – Bodhi Oct 14 '17 at 13:28
@coffemug there isn’t anything like that in SQL Server. Except possibly sqlcmd variables. – Martin Smith Oct 14 '17 at 14:35

score 1 · Answer 2 · answered Oct 14 '17 at 13:42

1

Since SQL Server 2008, SQL Server has supported synonyms.

You can read about the syntax here.

You can read about the ideas here.

I'm not really a fan of synonyms. I find databases harder to navigate when the same object has different names in different places -- and I cannot readily see the mapping (as with aliases in a single query).

answered Oct 14 '17 at 13:42

Gordon Linoff

1,242,037
58
646
786

Synonyms are not supported for database names – Martin Smith Oct 14 '17 at 14:30
@MartinSmith . . . But they are supported for table names, so that might still help the OP, sort of. – Gordon Linoff Oct 14 '17 at 14:37
@GordonLinoff . yes – Bodhi Oct 14 '17 at 19:24

JohnRC · Answer 3 · 2017-10-16T21:02:18.023

-1

Another possibility is to use views to provide the link to tables in other databases. Use naming conventions to identify which database the view is linked to. This also helps to insulate the code from any changes to database names.

CREATE VIEW vOD_deviceid AS SELECT * FROM [otp-devices].dbo.deviceid ;

and

CREATE VIEW vSC_alarmmodlink AS SELECT * FROM mr1ghp/security.dbo.alarmmodlink

Then elsewhere

SELECT * FROM vOD_deviceid; -- OD prefix identifies the database
SELECT module FROM vSC_alarmmodlink;

Just substitute the view name wherever you would have used the full database.schema.tablename.

edited Oct 16 '17 at 21:02

answered Oct 14 '17 at 14:31

JohnRC

1,251
1
11
12

This can be done for repetitive queries, and i do have some views for the purpose but most of the time our requirement changes and we dont have a particular query for our purpose – Bodhi Oct 14 '17 at 19:28
I mean that once you have defined the baseline view of the table, then you always use the view instead of referring directly to the table - even in other views. So the view is the single local representation of the table in the other database. You don't create a different view for every query you have, that would defeat the objective. – JohnRC Oct 16 '17 at 12:03

How to shorten or alias database names in your query?

3 Answers3