Referencing Objects and the Maximum Number of Prefixes

Objects like tables or views can be referenced using the dot (.) notation based on this pattern: [{server}].[{database}].[{schema}].[{object}], e.g. [MyServer1].[MyDatabase1].[dbo].[MyTable1]. One will expect that this sequence should go from right to left and parts can’t be skipped. But we can have much more fun with it in the reality.

When you will review all these SELECT statements, they are valid and will return table data:

SELECT * FROM sys.tables
SELECT * FROM .sys.tables
SELECT * FROM ..sys.tables

SELECT * FROM master.sys.tables
SELECT * FROM .master.sys.tables
SELECT * FROM [your_server_name].master.sys.tables

Let’s take the code from the line marked and think about it: It looks like the starting dot is handled like to be an empty server name before it. If we will add something there then SQL Server will search for it in sys.servers system table if the server with such a name exists. If not, it will raise error 7202:

SELECT * FROM [.].master.sys.tables

If we will use the dot (.) without square brackets or add another dot (or anything else) before, a different error message 117 will be fired because the maximum number of allowed object name prefixes is 3.

SELECT * FROM ..master.sys.tables
SELECT * FROM ...sys.tables

What do you think about it? Is the query SELECT * FROM ..sys.tables like a bad joke and you will remove two starting dots automatically thinking it’s a typo? Let me know in the comments:)

Leave a Reply

Your email address will not be published. Required fields are marked *