ID Number: Q78816
1.11
OS/2
buglist1.11
Summary:
Problem ID: PRSQL9112003
SYMPTOMS
When issuing sp_helptext on a table, the "index_keys" column of
output may truncate the column names in the list.
CAUSE
When sp_helptext is issued, it produces a report consisting of
three columns: index_name, index_description, and index_keys. The
index_keys column is defined to be a maximum of 70 characters long.
Therefore, only the first 70 characters of column names are
displayed in the index_keys column of output. If the sum of the
lengths of the column names (plus a comma and space between each)
in the index is greater than 70 characters, any characters after
the first 70 will not be displayed in the output.
Because the sp_help procedure uses the sp_helpindex procedure to
produce the index information for a table, the output from sp_help
is also truncated.
WORKAROUND
If this limitation affects output, the text of the sp_helpindex
procedure can be changed to make the index_keys column larger. To
do this, perform the following steps:
1. While in the master database, extract the text of the
sp_helpindex procedure using sp_helptext. For example:
sp_helptext sp_helpindex
2. Save the text to a file.
3. Drop the sp_helpindex procedure (DROP PROC sp_helpindex).
4. Edit the saved text file to delete all non-Transact-SQL wording
(such as "rows affected").
5. Locate the section of text shown below:
create table #spindtab
(
index_name varchar(30),
index_keys varchar(70),
index_description varchar(68)
)
6. Change the datatype for index_keys from "varchar(70)" to a
length sufficient for the index column name lengths [a maximum
of varchar(255)].
7. Run the procedure. This will re-create the sp_helpindex
procedure, using the new value for the length of the index_keys
column.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version
1.11. This problem does not occur in SQL Server version 4.2.
Additional reference words: sp_helpindex sp_help