Lab 6 Solution

# The University of Texas

CS 327e

Fall 2004

### Lab 6 Solution

CREATE PROCEDURE TABLE_VIEW_INFO

@table_view_name nvarchar(50) = NULL

AS

BEGIN

SET ANSI_DEFAULTS ON

/* Check to see if no value is entered by the user */

IF @table_view_name IS NULL

BEGIN

PRINT 'No name was entered. Please enter a table or a view name'

END

ELSE

BEGIN

/* Check to see if the value entered is a VIEW */

IF @table_view_name IN ( SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.VIEWS

WHERE UPPER(TABLE_NAME) = UPPER(@table_view_name)

)

PRINT 'The value ' + UPPER(@table_view_name) + ' is a VIEW NAME and the names of the columns of the VIEW are as follows:'

ELSE

/* Check to see if the value entered is a TABLE */

IF @table_view_name IN ( SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE UPPER(TABLE_NAME) = UPPER(@table_view_name)

)

PRINT 'The value ' + UPPER(@table_view_name) + ' is a TABLE NAME and the names of the columns of the TABLE are as follows:'

ELSE

/* Value entered is neither a TABLE nor a VIEW */

BEGIN

PRINT @table_view_name + ' is neither a TABLE NAME nor a VIEW NAME'

RETURN

END

/* The value entered is either a TABLE or a VIEW */

/* Declare the variable to store value of column name */

DECLARE @column_name nvarchar(50)

/* Declare the cursor, which is used to traverse through the column names picking the next name (which is in the table/view) each time */

DECLARE column_cursor CURSOR FOR

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE UPPER(TABLE_NAME) = UPPER(@table_view_name)

ORDER BY COLUMN_NAME ASC

OPEN column_cursor

/* Fetch the first column and store its value */

FETCH NEXT FROM column_cursor INTO @column_name

/* Traverse through the alphabetically ordered list of column names until it is exhausted */

WHILE @@FETCH_STATUS = 0

BEGIN

/* Print the name of the next column */

PRINT @column_name

/* Try to fetch the next column name from the ordered list */

FETCH NEXT FROM column_cursor INTO @column_name

END

END

END

GO