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.
Table of Contents
Fully Qualified Table Name
The fully qualified table name in SQL Server has three parts as shown below
1 2 3 | database_name.schema_name.table_name |
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
1 2 3 | Create database HR; |
Select the database
1 2 3 | Use HR; |
Create a new table Employee
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE Employee ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Department varchar(20) NOT NULL, Designation varchar(15) NOT NULL, Salary decimal(10, 0) NOT NULL, Email varchar(50) NULL, ) |
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
1 2 3 | create schema Sales |
Now, create the Employee Table under Sales schema <schema_name> statement.
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE Sales.Employee ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Department varchar(20) NOT NULL, Designation varchar(15) NOT NULL, Salary decimal(10, 0) NOT NULL, Email varchar(50) NULL, ) |
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
1 2 3 | Create database Support |
You can now create a Employee Table in the Support database under the dbo schema
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE Support.dbo.Employee ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Department varchar(20) NOT NULL, Designation varchar(15) NOT NULL, Salary decimal(10, 0) NOT NULL, Email varchar(50) NULL, ) |
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
Read More
- Create Database
- Drop Database
- Create Table
- Fully Qualified Table Names
- Drop Table
- Adding a Row to a Table
