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