Search code examples
sqlsql-servert-sqldatabase-schema

What does double dots .. mean in SQL Server?

I am using SQL Server. I found the following way to backup a database table:

-- Taking a backup    
SELECT * INTO MY_BACKUP_DATABASE..CustomersTemporaryTable FROM Customers

I am trying to understand the .. in the syntax. From what I understand, the sentence means that Customers is the table that is going to be backed-up by placing it all of its content into the database called MY_BACKUP_DATABASE using CustomersTemporaryTable as the destination table. I assume when executing the sentence, CustomersTemporaryTable must already exist. Is my understanding of the sentence to take a backup correct?

Solution

  • Each MS SQL Table identifiers can have a name compound of three parts separates with a dot :

    • the database name
    • the SQL schema name (by default dbo)
    • the table, view or Table UDF name

    Syntax :

    db_name.schema_name.table_name

    But it is not always necessary to specify the three parts.

    Inside the current database, no need to specify the db_name. It's implicit... By default every SQL user is associate with a specific default schema (frequently dbo too...).

    So you can specify a table name with :

    schema_name.table_name

    ...SQL Server will try to find the table into the current DB

    db_name..table_name

    ...SQL Server will try to find the table into the specified DB and the default user schema

    table_name

    ...SQL Server will try to find the table into the current DB and the default user schema

    To know with SQL schema is associated with your SQL user, use :

    SELECT SCHEMA_NAME() AS DEFAULT_CURRENT_USER_SCHEMA

    To know all the associations between SQL users and SQL schemas, do :

    SELECT name AS USER_NAME, default_schema_name
FROM   sys.database_principals
WHERE  type_desc LIKE '%?_USER' ESCAPE '?'