Edit

Share via

Lesson 3: Delete database objects

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW) SQL database in Microsoft Fabric

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 SQL Server 2005 (9.x):

    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 Transact-SQL 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