Skip to content

Latest commit

 

History

History
103 lines (75 loc) · 3 KB

lesson-3-deleting-database-objects.md

File metadata and controls

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

Lesson 3: Delete database objects

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

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