How to Create & Transfer Schemas in SQL Server (Practical) | T-SQL Course Part 17

Описание: Move from theory to practice! In this tutorial, we get hands-on with T-SQL to manage database schemas. I will show you exactly how to create custom logical containers and move your tables between them using the TRANSFER command.

What you will learn in this hands-on lesson:

Schema Creation: The syntax for building custom containers like [HR] or [Sales].

The TRANSFER Command: How to move a table from the default 'dbo' schema to your new custom schema.

Schema Verification: How to write a query to see a list of all tables and their assigned schemas.

Best Practices: Why prefixing your table names with the schema name is essential for professional development.

Troubleshooting: Understanding why you must use the schema name in your SELECT statements after moving a table.

T-SQL PRACTICAL SCRIPT
-- 1. CREATING A NEW SCHEMA
-- This creates a logical "folder" for your objects
CREATE SCHEMA [HR]
GO

-- 2. MOVING A TABLE TO THE NEW SCHEMA
-- Syntax: ALTER SCHEMA [NewSchema] TRANSFER [OldSchema].[TableName]
-- In this example, we move 'EmployeeDetails' from 'dbo' to 'HR'
ALTER SCHEMA [HR] TRANSFER [dbo].[EmployeeDetails]

-- 3. VIEWING OBJECTS INSIDE SCHEMAS
-- We join two system views to see exactly where our tables live
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
ORDER BY SchemaName;

-- 4. QUERYING THE MOVED TABLE
-- Note: You must now use the schema prefix
SELECT * FROM [HR].[EmployeeDetails]

#SQLServer #TSQL #DatabaseManagement #SQLSchema #SSMS #LearnSQL #DatabaseAdmin #SQLTutorial #DataEngineering