Fall 2004
Due: 2004-11-15
Monday 8.30 a.m. in class and in your group account.
PURPOSE:
The purpose of
this lab is to learn about Stored Procedures in SQL Server. This is a group submission. In other
words, your group needs to submit 1 copy, and you are free and encouraged to
collaborate within your group.
DESCRIPTION/IMPLEMENTATION:
Consider the SQL Server Catalog. The catalog comprises of various
built-in “system tables” with the prefix “sys”. In
order for users to query these system catalog tables, there are built-in views
in SQL Server called “Information Schema”.
The description of these system tables can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp
The description of these views can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp
Write a procedure that takes in as input, an object name.
The procedure is supposed to verify (by querying the information
schema) whether the input object name is
·
An
existing table name,
·
An
existing view name,
·
Or
neither
The procedure then prints this fact.
In the case where it is a table or a view name, the procedure then
prints the names of all the columns (in alphabetical order) belonging to that
view/table name.
Test this procedure using some tables/views that you might have
created in your group account.
The default value for the input parameter is NULL.
NOTE: All character comparisons need to be
case-insensitive (HINT: Use UPPER or LOWER built-in function of SQL Server).
NOTE: For non-NULL input values, you need to check
if they exist. If not, return a user-friendly message containing the name that
doesn’t exist.
NOTE: Provide adequate documentation inside the
procedure. State your assumptions clearly.
Submission:
Print a Word copy of the CREATE PROCEDURE and submit
in class. In addition, the TA will be logging into your group account, and
verifying various use cases as inputs.
Last Updated: 11/4/2004 11:17:22 AM