Lab 5

# CS327e

# Lab 5

Spring 2005

Due: 2005-04-08 Friday 8.30 a.m. in class and in your group account. NO LATE SUBMISSIONS ALLOWED.

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:

The information used by Microsoft® SQL Server™ 2000 and its components is stored in special tables known as system tables.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

System tables (e.g. SYSOBJECTS, SYSCOLUMNS, SYSCOMMENTS, and SYSREFERENCES etc.) should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers. Reference of documented columns in system tables is permissible.

Although one should use SQL Server’s predefined views to access the system tables

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp

(since the structure of these SYSTEM tables could change from one release to the other, we will use the system tables for the purposes of the lab.

In this Procedure that you will write, you will be given an object name as input.

The Procedure will then determine if the name exists in the SYSOBJECTS table. If not, it will print a descriptive error message. If it does exist, it will print the kind of object (as obtained from SYSOBJECTS.XTYPE) it is.

Additionally, if the object exists,

If the object is a TABLE or a VIEW, it will then print the names of all its columns, along with their data types, size, and whether NULLs are allowed.

If the object is a PROCEDURE, it will print all its parameter names, types, size, whether they are input or output, and whether NULLs are allowed.

If the object is a FOREIGN KEY, the names of the table.column(s) comprising the foreign key, along with their corresponding referenced primary key table.column (s), in that order.

The procedure will return a status parameter: value will be 0 if object is found, -1 if not found.

CLEARLY DOCUMENT YOUR PROCEDURE, AND STATE ANY ASSUMPTIONS.

Here are some sample code and test cases to assist you in your development.

/*

**************************** DROP PROCEDURE LAB5_TYPE_U_V_S_DETAILS *******************

*/

IF EXISTS

(SELECT

NAME

FROM

SYSOBJECTS

WHERE

UPPER(RTRIM(NAME)) = 'LAB5_TYPE_U_V_S_DETAILS'

AND

TYPE = 'P')

/* If Procedure exists, Drop it */

DROP PROCEDURE LAB5_TYPE_U_V_S_DETAILS

GO

/*

**************************** PROCEDURE :: LAB5_TYPE_U_V_S_DETAILS *********************

NAME: LAB5_TYPE_U_V_S_DETAILS

DESCRIPTION: Given an object name '@Obj_Name' of type

USER TABLE (U),

SYSTEM TABLE (S),

VIEW (V)

this procedure determines information about its columns. This includes

COL_NAME - The name of the column

DATA_TYPE - Its data type

PRECISION - Precision of its data type

SCALE - Scale of its data type

ALLOWS_NULLS- Whether the column allows NULLs or not.

The system tables accessed to get this information include

SYSOBJECTS

SYSCOLUMNS

SYSTYPES

This information is displayed with the help of PRINT statements.

To facilitate traversing through each row and printing its contents,

a cursor is used along with local variables.

ASSUMPTIONS: The input object name already exists in the database and is not null.

The size of the input object name is restricted to 255 chars.

The input object is of one of the 3 types listed above.

The 'View' object is not made up of other views.

INPUT PARAM: @Obj_Name nvarchar (255)

Name of the table or view.

OUTPUT PARAM:

***************************** PROCEDURE :: LAB5_TYPE_U_V_S_DETAILS *********************

*/

/* Header of the Stored Procedure */

CREATE PROCEDURE LAB5_TYPE_U_V_S_DETAILS

@Obj_Name nvarchar (255)

AS

BEGIN

/* TO BE FILLED BY YOU */

END

GO

/*

**************************** DROP PROCEDURE LAB5_TYPE_P_X_DETAILS *******************

*/

IF EXISTS

(SELECT

NAME

FROM

SYSOBJECTS

WHERE

UPPER(RTRIM(NAME)) = 'LAB5_TYPE_P_X_DETAILS'

AND

TYPE = 'P')

/* If Procedure exists, Drop it */

DROP PROCEDURE LAB5_TYPE_P_X_DETAILS

GO

/*

**************************** PROCEDURE :: LAB5_TYPE_P_X_DETAILS *********************

NAME: LAB5_TYPE_P_X_DETAILS

DESCRIPTION: Given an object name '@Obj_Name' of type

STORED PROCEDURE (P),

EXTENDED STORED PROCEDURE (X)

this procedure determines information about its parameters.

This includes

PARM_NAME - The name of the parameter

DATA_TYPE - Its data type

PRECISION - Precision of its data type

SCALE - Scale of its data type

ALLOWS_NULLS - Whether the parameter allows NULLs or not

INPUT_OUTPUT - Whether the parameter is INPUT or OUTPUT

The system tables accessed to get this information include

SYSOBJECTS

SYSCOLUMNS

SYSTYPES

This information is displayed with the help of PRINT statements.

To facilitate traversing through each row and printing its contents,

a cursor is used along with local variables.

ASSUMPTIONS: The input object name already exists in the database and is not null.

The size of the input object name is restricted to 255 chars.

The input object is of STORED PROCEDURE type.

INPUT PARAM: @Obj_Name nvarchar (255)

Name of the stored procedure.

OUTPUT PARAM:

**************************** PROCEDURE :: LAB5_TYPE_P_X_DETAILS *********************

*/

/* Header of the Stored Procedure */

CREATE PROCEDURE LAB5_TYPE_P_X_DETAILS

@Obj_Name nvarchar (255)

AS

BEGIN

/* TO BE FILLED BY YOU */

END

GO

/*

**************************** DROP PROCEDURE LAB5_TYPE_F_DETAILS *******************

*/

IF EXISTS

(SELECT

NAME

FROM

SYSOBJECTS

WHERE

UPPER(RTRIM(NAME)) = 'LAB5_TYPE_F_DETAILS'

AND

TYPE = 'P')

/* If Procedure exists, Drop it */

DROP PROCEDURE LAB5_TYPE_F_DETAILS

GO

/*

**************************** PROCEDURE :: LAB5_TYPE_F_DETAILS *********************

NAME: LAB5_TYPE_F_DETAILS

DESCRIPTION: Given an object name '@Obj_Name' of type

FOREIGN KEY (F)

this procedure determines information about its Table/Column, as well

as the referenced Table/Column of its parent. This includes

REFERENCING_T_NAME - The name of the CHILD TABLE containing the FK

REFERENCING_C_NAME - The name of the CHILD COLUMN

REFERENCED_T_NAME - The name of the PARENT TABLE containing the

PK that the FK references

REFERENCED_C_NAME - The name of the PARENT PK COLUMN that the FK

CHILD COLUMN references.

The system tables accessed to get this information include

SYSOBJECTS

SYSCOLUMNS

SYSFOREIGNKEYS

This information is displayed with the help of PRINT statements.

To facilitate traversing through each row and printing its contents,

a cursor is used along with local variables.

Two self-joins are accomplished in the SELECT statement to determine

the appropriate output.

ASSUMPTIONS: The input object name already exists in the database and is not null.

The size of the input object name is restricted to 255 chars.

The input object is of one of the 3 types listed above.

The 'View' object is not made up of other views.

INPUT PARAM: @Obj_Name nvarchar (255)

Name of the foreign key.

OUTPUT PARAM:

**************************** PROCEDURE :: LAB5_TYPE_F_DETAILS *********************

*/

/* Header of the Stored Procedure */

CREATE PROCEDURE LAB5_TYPE_F_DETAILS

@Obj_Name nvarchar (255)

AS

BEGIN

DECLARE @REFERENCING_T_NAME char (25)

DECLARE @REFERENCING_C_NAME char (25)

DECLARE @REFERENCED_T_NAME char (25)

DECLARE @REFERENCED_C_NAME char (25)

PRINT 'Object <' + @Obj_Name + '> has the following Columns:'

PRINT ' '

DECLARE F_CURSOR CURSOR FOR

SELECT

convert (char (25), REFERENCING_T.NAME) REFERENCING_T_NAME,

convert (char (25), REFERENCING_C.NAME) REFERENCING_C_NAME,

convert (char (25), REFERENCED_T.NAME) REFERENCED_T_NAME,

convert (char (25), REFERENCED_C.NAME) REFERENCED_C_NAME

FROM

SYSFOREIGNKEYS FK,

SYSOBJECTS O,

SYSOBJECTS REFERENCING_T,

SYSOBJECTS REFERENCED_T,

SYSCOLUMNS REFERENCING_C,

SYSCOLUMNS REFERENCED_C

WHERE

FK.CONSTID = O.ID

AND

FK.FKEYID = REFERENCING_T.ID

AND

FK.RKEYID = REFERENCED_T.ID

AND

FK.FKEY = REFERENCING_C.COLID

AND

FK.FKEYID = REFERENCING_C.ID

AND

FK.RKEY = REFERENCED_C.COLID

AND

FK.RKEYID = REFERENCED_C.ID

AND

UPPER(RTRIM(O.NAME)) = UPPER(RTRIM(@Obj_Name))

ORDER BY FK.KEYNO

OPEN F_CURSOR

/* Point the cursor to the first row */

FETCH NEXT FROM F_CURSOR

INTO @REFERENCING_T_NAME, @REFERENCING_C_NAME, @REFERENCED_T_NAME, @REFERENCED_C_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT RTRIM(@REFERENCING_T_NAME) + '.' + RTRIM(@REFERENCING_C_NAME) + ' REFERENCES ' +

RTRIM(@REFERENCED_T_NAME) + '.' + RTRIM(@REFERENCED_C_NAME)

FETCH NEXT FROM F_CURSOR

INTO @REFERENCING_T_NAME, @REFERENCING_C_NAME, @REFERENCED_T_NAME, @REFERENCED_C_NAME

END

CLOSE F_CURSOR

DEALLOCATE F_CURSOR

END

GO

/*

**************************** DROP PROCEDURE LAB5_OBJECT_TYPE *******************

*/

IF EXISTS

(SELECT

NAME

FROM

SYSOBJECTS

WHERE

UPPER(RTRIM(NAME)) = 'LAB5_OBJECT_TYPE'

AND

TYPE = 'P')

/* If Procedure exists, Drop it */

DROP PROCEDURE LAB5_OBJECT_TYPE

GO

/*

**************************** PROCEDURE :: LAB5_OBJECT_TYPE *********************

NAME: LAB5_OBJECT_TYPE

DESCRIPTION: Given an object name '@Obj_Name',

this procedure prints and returns its type.

The system tables accessed to get this information include

SYSOBJECTS

This information is displayed with the help of PRINT statements.

If the object type is indeterminate, an error message is displayed.

ASSUMPTIONS: The input object name already exists in the database and is not null.

The size of the input object name is restricted to 255 chars.

The input object is of one of the many types listed below.

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

IF = Inlined table-function

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User table

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure

INPUT PARAM: @Obj_Name nvarchar (255)

Name of the object.

OUTPUT PARAM: @Object_Type varchar (2) OUTPUT

Type of object.

**************************** PROCEDURE :: LAB5_OBJECT_TYPE *********************

*/

/* Header of the Stored Procedure */

CREATE PROCEDURE LAB5_OBJECT_TYPE

@Obj_Name nvarchar (255),

@Object_Type varchar (2) OUTPUT

AS

BEGIN

/* TO BE FILLED BY YOU */

END

GO

/*

**************************** DROP PROCEDURE LAB5 *******************************

*/

IF EXISTS

(SELECT

NAME

FROM

SYSOBJECTS

WHERE

UPPER(RTRIM(NAME)) = 'LAB5'

AND

TYPE = 'P')

/* If Procedure exists, Drop it */

DROP PROCEDURE LAB5

GO

/*

**************************** PROCEDURE :: LAB5 **********************************

NAME: LAB5

DESCRIPTION: Given an object name '@Obj_Name' , it determines

a) If the object exists. If it exists,

b) Determines its type. If object is a TABLE, VIEW, PROCEDURE, or

a FK,

c) it determines additional info about it.

If Object does not exists, it returns a status of -1, otherwise 0.

It invokes the following procedures to determine b) and c) above:

LAB5_OBJECT_TYPE

LAB5_TYPE_U_V_S_DETAILS

LAB5_TYPE_P_X_DETAILS

LAB5_TYPE_F_DETAILS

The system tables accessed to get this information include

SYSOBJECTS

This information is displayed with the help of PRINT statements.

ASSUMPTIONS: The input object name may or may not exists in the database and could be null.

The size of the input object name is restricted to 255 chars.

INPUT PARAM: @Obj_Name nvarchar (255) = NULL

Name of the object.

OUTPUT PARAM: @Status int OUTPUT

0 - If object exits.

-1 - If object does not exists in SYSOBJECTS.

**************************** PROCEDURE :: LAB5 **********************************

*/

/* Header of the Stored procedure */

CREATE PROCEDURE LAB5

/* Set the defaults to be NULL for all INPUT parameters */

@Object_Name nvarchar (255) = NULL,

@Status int OUTPUT

AS

BEGIN /* Begin Body of the Stored procedure */

/*

The following SET statement is needed for ANSI compatibility for NULL values, single quotes, implicit transactions etc. For more details, see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_2uw7.asp

*/

SET ANSI_DEFAULTS ON

/* TO BE FILLED BY YOU */

END /* End Body of the Stored procedure */

GO

/*

******************** TEST CASES **************************************

******************** TEST CASES **************************************

******************** TEST CASES **************************************

NOTE :: EACH OF THESE TEST CASES ARE EXECUTED FROM THE QUERY ANALYZER.

*/

/*

TEST CASE 1:: Test a User Defined Table

---------------------------------------

DECLARE @status int

Exec LAB5 'PART', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 1:: OUTPUT

--------------------

Object <PART> exists in the database.

Object <PART> is a User table.

Object <PART> has the following Columns:

COL_NAME DATA_TYPE PRECISION SCALE ALLOWS_NULLS

City nvarchar 255 0 TRUE

Color nvarchar 255 0 TRUE

Name nvarchar 255 0 TRUE

PID nvarchar 25 0 FALSE

Weight numeric 5 2 TRUE

Status = 0

*/

/*

TEST CASE 2:: Test a System Table

---------------------------------

DECLARE @status int

Exec LAB5 'SysTypes', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 2:: OUTPUT

--------------------

Object <SysTypes> exists in the database.

Object <SysTypes> is a system table.

Object <SysTypes> has the following Columns:

COL_NAME DATA_TYPE PRECISION SCALE ALLOWS_NULLS

allownulls bit 1 0 TRUE

collation sysname 128 0 TRUE

collationid int 10 0 TRUE

domain int 10 0 FALSE

length smallint 5 0 FALSE

name sysname 128 0 FALSE

prec smallint 5 0 TRUE

printfmt varchar 255 0 TRUE

reserved smallint 5 0 FALSE

scale tinyint 3 0 TRUE

status tinyint 3 0 FALSE

tdefault int 10 0 FALSE

type tinyint 3 0 TRUE

uid smallint 5 0 FALSE

usertype smallint 5 0 TRUE

variable bit 1 0 TRUE

xprec tinyint 3 0 FALSE

xscale tinyint 3 0 FALSE

xtype tinyint 3 0 FALSE

xusertype smallint 5 0 FALSE

Status = 0

*/

/*

TEST CASE 3:: Test a PK

-----------------------

DECLARE @status int

Exec LAB5 'shipment_pk', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 3:: OUTPUT

--------------------

Object <shipment_pk> exists in the database.

Object <shipment_pk> is a PRIMARY KEY constraint.

Status = 0

*/

/*

TEST CASE 4:: Test a FK

-----------------------

DECLARE @status int

Exec LAB5 'part_shipment_fk', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 4:: OUTPUT

--------------------

Object <part_shipment_fk> exists in the database.

Object <part_shipment_fk> is a FOREIGN KEY constraint.

Object <part_shipment_fk> has the following Columns:

SHIPMENT.PID REFERENCES PART.PID

Status = 0

*/

/*

TEST CASE 5:: Test a Non-existent Object

----------------------------------------

DECLARE @status int

Exec LAB5 'DoesNotExists', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 5:: OUTPUT

--------------------

Object <DoesNotExists> does not exist in the database.

Status = -1

*/

/*

TEST CASE 6:: Test a View

-------------------------

DECLARE @status int

Exec LAB5 'v1', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 6:: OUTPUT

--------------------

Object <v1> exists in the database.

Object <v1> is a View.

Object <v1> has the following Columns:

COL_NAME DATA_TYPE PRECISION SCALE ALLOWS_NULLS

SID nvarchar 255 0 TRUE

Status = 0

*/

/*

TEST CASE 7:: Test a Procedure

------------------------------

DECLARE @status int

Exec LAB5 'lab5', @status OUTPUT

PRINT 'Status = ' + convert (char(20), @status)

TEST CASE 7:: OUTPUT

--------------------

Object <lab5> exists in the database.

Object <lab5> is a Stored procedure.

Object <lab5> has the following Parameters:

PARM_NAME DATA_TYPE PRECISION SCALE ALLOWS_NULLS INPUT_OUTPUT

@Object_Name nvarchar 255 0 TRUE INPUT

@Status int 10 0 TRUE OUTPUT

Status = 0

*/

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: 4/4/2005 9:34:35 PM