Lesson 3: Delete database objects
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Analytics Platform System (PDW)
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
Use the
DROP
statement to remove permission forMary
to access theTestData
database:DROP USER Mary; GO
Use the
DROP
statement to remove permission forMary
to access this instance of SQL Server 2005 (9.x):DROP LOGIN [<computer_name>\Mary]; GO
Use the
DROP
statement to remove the store procedurepr_Names
:DROP PROC pr_Names; GO
Use the
DROP
statement to remove the viewvw_Names
:DROP VIEW vw_Names; GO
Delete table
Use the
DELETE
statement to remove all rows from theProducts
table:DELETE FROM Products; GO
Use the
DROP
statement to remove theProducts
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.