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
DECLARE @COLUMN_NAME sysname
DECLARE @DATA_TYPE sysname
DECLARE @DATATYPE_PRECISION char (12)
DECLARE @message nvarchar(255)
DECLARE @DATATYPE_SCALE char (12)
DECLARE @ALLOWS_NULLS char (5)
PRINT 'Object <' + @Obj_Name + '> has the following Columns:'
PRINT ' '
DECLARE U_V_S_CURSOR CURSOR FOR
SELECT DISTINCT
C.NAME COLUMN_NAME,
T.NAME DATA_TYPE,
convert
(char (12), ISNULL (C.PREC, 0)) DATATYPE_PRECISION,
convert
(char (12), ISNULL (C.SCALE, 0)) DATATYPE_SCALE,
CASE C.ISNULLABLE when 1 then 'TRUE' when 0 then 'FALSE'
when NULL then 'NULL' end ALLOWS_NULLS
FROM
SYSOBJECTS O,
SYSCOLUMNS C,
SYSTYPES T
WHERE
C.XTYPE = T.XTYPE
AND
C.USERTYPE = T.USERTYPE
AND
C.ID = O.ID
AND
UPPER(RTRIM(O.NAME))
= UPPER(RTRIM(@Obj_Name))
OPEN U_V_S_CURSOR
/* Point the cursor to the first row */
FETCH NEXT FROM U_V_S_CURSOR
INTO @COLUMN_NAME, @DATA_TYPE,
@DATATYPE_PRECISION, @DATATYPE_SCALE, @ALLOWS_NULLS
PRINT 'COL_NAME DATA_TYPE PRECISION SCALE
ALLOWS_NULLS'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message =
convert
(char (25), @COLUMN_NAME) + ' ' +
convert
(char (25), @DATA_TYPE) + ' ' +
convert (char
(10), @DATATYPE_PRECISION) + ' ' +
convert
(char (10), @DATATYPE_SCALE) + ' ' +
@ALLOWS_NULLS
PRINT @message
FETCH NEXT FROM U_V_S_CURSOR
INTO @COLUMN_NAME, @DATA_TYPE,
@DATATYPE_PRECISION, @DATATYPE_SCALE, @ALLOWS_NULLS
END
CLOSE U_V_S_CURSOR
DEALLOCATE U_V_S_CURSOR
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
DECLARE @PARM_NAME sysname
DECLARE @DATA_TYPE sysname
DECLARE @DATATYPE_PRECISION char (12)
DECLARE @message nvarchar(255)
DECLARE @DATATYPE_SCALE char (12)
DECLARE @ALLOWS_NULLS char (5)
DECLARE @INPUT_OUTPUT char (6)
PRINT 'Object <' + @Obj_Name + '> has the following Parameters:'
PRINT ' '
DECLARE P_X_CURSOR CURSOR FOR
SELECT DISTINCT
C.NAME PARM_NAME,
T.NAME DATA_TYPE,
convert
(char (12), ISNULL (C.PREC, 0)) DATATYPE_PRECISION,
convert
(char (12), ISNULL (C.SCALE, 0)) DATATYPE_SCALE,
CASE C.ISNULLABLE when 1 then 'TRUE' when 0 then 'FALSE' when
NULL then 'NULL' end ALLOWS_NULLS,
CASE C.ISOUTPARAM when 1 then 'OUTPUT' when 0 then 'INPUT' when
NULL then 'NULL' end INPUT_OUTPUT
FROM
SYSOBJECTS O,
SYSCOLUMNS C,
SYSTYPES T
WHERE
C.XTYPE = T.XTYPE
AND
C.USERTYPE = T.USERTYPE
AND
C.ID = O.ID
AND
UPPER(RTRIM(O.NAME))
= UPPER(RTRIM(@Obj_Name))
OPEN P_X_CURSOR
/* Point the cursor to the first row */
FETCH NEXT FROM P_X_CURSOR
INTO @PARM_NAME, @DATA_TYPE,
@DATATYPE_PRECISION, @DATATYPE_SCALE, @ALLOWS_NULLS, @INPUT_OUTPUT
PRINT 'PARM_NAME DATA_TYPE PRECISION SCALE
ALLOWS_NULLS INPUT_OUTPUT'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message =
convert
(char (25), @PARM_NAME) +
' ' +
convert
(char (25), @DATA_TYPE) +
' ' +
convert
(char (10), @DATATYPE_PRECISION) + ' ' +
convert
(char (10), @DATATYPE_SCALE) + ' ' +
convert
(char (5), @ALLOWS_NULLS) +
' ' +
@INPUT_OUTPUT
PRINT @message
FETCH NEXT FROM P_X_CURSOR
INTO @PARM_NAME, @DATA_TYPE,
@DATATYPE_PRECISION, @DATATYPE_SCALE, @ALLOWS_NULLS, @INPUT_OUTPUT
END
CLOSE P_X_CURSOR
DEALLOCATE P_X_CURSOR
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
/* Get the Object Type */
/*
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
*/
SELECT
@Object_Type = UPPER
(RTRIM (XTYPE))
FROM
SYSOBJECTS
WHERE
UPPER
(RTRIM (NAME)) = UPPER (RTRIM (@Obj_Name))
IF @Object_Type = 'C'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a CHECK Constraint.'
END
ELSE IF @Object_Type = 'D'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Default or DEFAULT constraint.'
END
ELSE IF @Object_Type = 'F'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a FOREIGN KEY constraint.'
END
ELSE IF @Object_Type = 'L'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Log.'
END
ELSE IF @Object_Type = 'FN'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Scalar function.'
END
ELSE IF @Object_Type = 'IF'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is an Inlined table-function.'
END
ELSE IF @Object_Type = 'P'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Stored procedure.'
END
ELSE IF @Object_Type = 'PK'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a PRIMARY KEY constraint.'
END
ELSE IF @Object_Type = 'RF'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Replication filter stored procedure.'
END
ELSE IF @Object_Type = 'S'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a system table.'
END
ELSE IF @Object_Type = 'TF'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Table function.'
END
ELSE IF @Object_Type = 'TR'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a Trigger.'
END
ELSE IF @Object_Type = 'U'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a User table.'
END
ELSE IF @Object_Type = 'UQ'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a UNIQUE constraint.'
END
ELSE IF @Object_Type = 'V'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is a View.'
END
ELSE IF @Object_Type = 'X'
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is an Extended stored procedure.'
END
ELSE
BEGIN
PRINT 'Object <' +
@Obj_Name + '> is of indeterminate type.'
END
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 */
/* Declare a local variable */
DECLARE @Object_Type char (2)
/*
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
/* Initialize the status to be FAILURE */
SET @status = -1
/* ------------------------------- Check
for NULL inputs and existence -------------------------- */
/* Check for Object being NULL */
IF @Object_Name IS NULL
BEGIN
PRINT 'No Object Name entered.'
END
ELSE
BEGIN
/* Check to see if Object exists */
IF UPPER (RTRIM (@Object_Name)) NOT
IN
(
SELECT
DISTINCT UPPER (RTRIM
(NAME))
FROM
SYSOBJECTS
)
BEGIN /* Object does not exists in
SYSOBJECTS */
PRINT 'Object <' +
@Object_Name + '> does not exist in the database.'
END
ELSE /* Object exists in SYSOBJECTS */
BEGIN
SET @status = 0
PRINT 'Object <' +
@Object_Name + '> exists in the database.'
EXEC LAB5_OBJECT_TYPE
@Object_Name, @Object_Type OUTPUT
/*
For Objects of Type
U - uSER DEFINED TABLE
S - SYSTEM TABLE
V - VIEW
get
additional info
*/
IF
(
(@Object_Type = 'U') OR
(@Object_Type = 'V') OR
(@Object_Type = 'S')
)
BEGIN
EXEC
LAB5_TYPE_U_V_S_DETAILS @Object_Name
END
/*
For
Objects of Type
P - STORED PROCEDURE
X - EXTENDED STORED PROCEDURE
get
additional info
*/
ELSE IF
(
(@Object_Type = 'X') OR
(@Object_Type = 'P')
)
BEGIN
EXEC
LAB5_TYPE_P_X_DETAILS @Object_Name
END
/*
For Objects of Type
F - FOREIGN KEY
get
additional info
*/
ELSE IF
(@Object_Type = 'F')
BEGIN
EXEC LAB5_TYPE_F_DETAILS
@Object_Name
END
END
END
PRINT ' '
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/8/2005 11:42:54 AM