lesson-3-deleting-database-objects.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	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	ignite-2024	deleting database objects	>=aps-pdw-2016 || =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

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:

   DROPUSER 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:

   DROPVIEW vw_Names; GO

Delete table

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

   DELETEFROM Products; GO

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

   DROPTABLE 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 DROPDATABASE 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

• Back Up and Restore of SQL Server Databases
• Maintenance Plans
• sp_configure (Transact-SQL)