sp_helptext的第二个参数是什么作用呀？
sp_helptext的作用是查看存贮过程的原代码，
但是帮助文件中，没有给出第二个参数的作用，我查看 sp_helptext 本身的原代码时，发现了这个参数，但是看不明白，是什么功能。
以下就是原代码:create procedure sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
asset nocount ondeclare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int --lengths of line feed carriage return
,@DefinedLength int/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)/*
** Make sure the @objname is local to the current database.
*/if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end/*
** See if @objname exists.
*/
if (object_id(@objname) is null)
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end-- If second parameter was given.
if ( @columnname is not null)
begin
-- Check if it is a table-- C = CHECK 约束
-- D = 默认值或 DEFAULT 约束
-- F = FOREIGN KEY 约束
-- L = 日志
-- FN = 标量函数
-- IF = 内嵌表函数
-- P = 存储过程
-- PK = PRIMARY KEY 约束（类型是 K）
-- RF = 复制筛选存储过程
-- S = 系统表
-- TF = 表函数
-- TR = 触发器
-- U = 用户表
-- UQ = UNIQUE 约束（类型是 K）
-- V = 视图
-- X = 扩展存储过程
if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
begin
raiserror(15218,-1,-1,@objname)
return(1)
end
-- check if it is a correct column name
if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
begin
raiserror(15645,-1,-1,@columnname)
return(1)
end
if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
begin
raiserror(15646,-1,-1,@columnname)
return(1)
end DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
(select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
order by number,colid
FOR READ ONLY end
else
begin
/*
** Find out how many lines of text are coming back,
** and return if there are none.
*/
if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
and o.id = c.id and o.id = object_id(@objname)) = 0
begin
raiserror(15197,-1,-1,@objname)
return (1)
end if (select count(*) from syscomments where id = object_id(@objname)
and encrypted = 0) = 0
begin
raiserror(15471,-1,-1)
return (0)
end DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
ORDER BY number, colid
FOR READ ONLY
end/*
** Else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscomFETCH NEXT FROM ms_crs_syscom into @SyscomTextWHILE @@fetch_status >= 0
BEGIN SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) --If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END FETCH NEXT FROM ms_crs_syscom into @SyscomText
ENDIF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )select Text from #CommentText order by LineIdCLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscomDROP TABLE #CommentTextreturn (0) -- sp_helptext
sp_helptext的作用是查看存贮过程的原代码，
但是帮助文件中，没有给出第二个参数的作用，我查看 sp_helptext 本身的原代码时，发现了这个参数，但是看不明白，是什么功能。
以下就是原代码:create procedure sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
asset nocount ondeclare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int --lengths of line feed carriage return
,@DefinedLength int/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)/*
** Make sure the @objname is local to the current database.
*/if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end/*
** See if @objname exists.
*/
if (object_id(@objname) is null)
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end-- If second parameter was given.
if ( @columnname is not null)
begin
-- Check if it is a table-- C = CHECK 约束
-- D = 默认值或 DEFAULT 约束
-- F = FOREIGN KEY 约束
-- L = 日志
-- FN = 标量函数
-- IF = 内嵌表函数
-- P = 存储过程
-- PK = PRIMARY KEY 约束（类型是 K）
-- RF = 复制筛选存储过程
-- S = 系统表
-- TF = 表函数
-- TR = 触发器
-- U = 用户表
-- UQ = UNIQUE 约束（类型是 K）
-- V = 视图
-- X = 扩展存储过程
if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
begin
raiserror(15218,-1,-1,@objname)
return(1)
end
-- check if it is a correct column name
if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
begin
raiserror(15645,-1,-1,@columnname)
return(1)
end
if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
begin
raiserror(15646,-1,-1,@columnname)
return(1)
end DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
(select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
order by number,colid
FOR READ ONLY end
else
begin
/*
** Find out how many lines of text are coming back,
** and return if there are none.
*/
if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
and o.id = c.id and o.id = object_id(@objname)) = 0
begin
raiserror(15197,-1,-1,@objname)
return (1)
end if (select count(*) from syscomments where id = object_id(@objname)
and encrypted = 0) = 0
begin
raiserror(15471,-1,-1)
return (0)
end DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
ORDER BY number, colid
FOR READ ONLY
end/*
** Else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscomFETCH NEXT FROM ms_crs_syscom into @SyscomTextWHILE @@fetch_status >= 0
BEGIN SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) --If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END FETCH NEXT FROM ms_crs_syscom into @SyscomText
ENDIF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )select Text from #CommentText order by LineIdCLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscomDROP TABLE #CommentTextreturn (0) -- sp_helptext
显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。语法
sp_helptext [ @objname = ] 'name'参数
[@objname =] 'name'对象的名称，将显示该对象的定义信息。对象必须在当前数据库中。name 的数据类型为 nvarchar(776)，没有默认值。 返回代码值
0（成功）或 1（失败）结果集
列名 数据类型 描述
Text nvarchar(255) 对象定义文本
注释
sp_helptext 在多个行中显示用来创建对象的文本，其中每行有 Transact-SQL 定义的 255 个字符。这些定义只驻留在当前数据库的 syscomments 表的文本中。权限
执行权限默认授予 public 角色。示例
下面的示例显示 employee_insupd 触发器的文本，该触发器在数据库 pubs 中。USE pubs
EXEC sp_helptext 'employee_insupd'
参数
[ @objname = ] 'name'
架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。name 的数据类型为 nvarchar(776)，无默认值。 [ @columnname = ] 'computed_column_name'
要显示其定义信息的计算列的名称。必须将包含列的表指定为 name。column_name 的数据类型为 sysname，无默认值。
[ @columnname = ] 'computed_column_name' 看看！
谁能兴举个例子，计算列的什么信息可以用它查看。