CS 327e
Fall 2004
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