Fully Qualified Table Names in SQL Server

The fully qualified table names in SQL Server consists of three parts. database name, schema name & the actual table name. The fully qualified table name must be UNIQUE in a SQL Server instance. We learned how to Create tables in the previous tutorial.

Fully Qualified Table Name

The fully qualified table name in SQL Server has three parts as shown below

table_name : is the actual table name (for example employee) that we gave it the table

schema_name: The schema to which this table belongs (for example dbo)

database_name: The database to which the schema belongs (for example hr)

Example

To Understand, let us create a new database (hr) and add a table to it (employee).

Connect to a SQL Server instance using the SSMS. Open the Query Editor

Create a new database HR

Select the database

Create a new table Employee

Now, from the object explorer, you will see that sql server lists employee table as dbo.employee.

The dbo is the schema, which the SQL Server creates automatically, when we create a new database. When we create a new table without a schema, then the SQL Server assigns the default schema to it.

Create table under another schema

We can create a new schema using the Create Schema statement

Now, create the Employee Table under Sales schema <schema_name> statement.

Create table under another schema

You can create table in another database. Create a new database Support. Remember the SQL Server automatically creates the default dbo schema

You can now create a Employee Table in the Support database under the dbo schema

Now, we have three employee tables under two databases. The Fully Qualified Table Names are HR.dbo.Employee, HR.Sales.Employee & Support.dbo.Employee.

The schema’s in SQL Server is a powerful thing. It allows us group database objects like tables, views, triggers, stored procedures, indexes, etc. We can manage them, assign permission to them etc.

Reference

Create Schema

Read More

  1. Create Database
  2. Drop Database
  3. Create Table
  4. Fully Qualified Table Names
  5. Drop Table
  6. Adding a Row to a Table

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top