Skip to content

lesson-3-deleting-database-objects.md

Latest commit

 

History

History
105 lines (77 loc) · 3.07 KB

lesson-3-deleting-database-objects.md

File metadata and controls

105 lines (77 loc) · 3.07 KB
title T-SQL Tutorial: Delete database objects
description This short lesson removes the objects that you created in Lesson 1 and Lesson 2, and then drops the database.
author MikeRayMSFT
ms.author mikeray
ms.reviewer randolphwest
ms.date 04/19/2023
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
helpviewer_keywords
deleting database objects
monikerRange >=aps-pdw-2016 || =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Lesson 3: Delete database objects

[!INCLUDEsql-asdb-asdbmi-pdw-fabricsqldb]

Note

The Get Started Querying with Transact-SQL learning path provides more in-depth content, along with practical examples.

This short lesson removes the objects that you created in Lesson 1 and Lesson 2, and then drops the database.

Before you delete objects, make sure you are in the correct database:

USE TestData;
GO

Revoke stored procedure permissions

Use the REVOKE statement to remove execute permission for Mary on the stored procedure:

REVOKE EXECUTE ON pr_Names FROM Mary;
GO

Drop permissions

  1. Use the DROP statement to remove permission for Mary to access the TestData database:

    DROP USER Mary;
GO

  2. Use the DROP statement to remove permission for Mary to access this instance of [!INCLUDEssVersion2005]:

    DROP LOGIN [<computer_name>\Mary];
GO

  3. Use the DROP statement to remove the store procedure pr_Names:

    DROP PROC pr_Names;
GO

  4. Use the DROP statement to remove the view vw_Names:

    DROP VIEW vw_Names;
GO

Delete table

  1. Use the DELETE statement to remove all rows from the Products table:

    DELETE FROM Products;
GO

  2. Use the DROP statement to remove the Products table:

    DROP TABLE Products;
GO

Remove database

You can't remove the TestData database while you are in the database; therefore, first switch context to another database, and then use the DROP statement to remove the TestData database:

USE MASTER;
GO
DROP DATABASE TestData;
GO

This concludes the Writing [!INCLUDEtsql] Statements tutorial. Remember, this tutorial is a brief overview and it doesn't describe all the options to the statements that are used. Designing and creating an efficient database structure and configuring secure access to the data requires a more complex database than that shown in this tutorial.

Next steps