· 6 years ago · Dec 19, 2019, 06:42 AM
1use master
2go
3
4alter procedure [dbo].[sp_helptext2]
5@objname nvarchar(776)
6,@columnname sysname = NULL
7as
8
9set nocount on
10
11declare @dbname sysname
12,@objid int
13,@BlankSpaceAdded int
14,@BasePos int
15,@CurrentPos int
16,@TextLength int
17,@LineId int
18,@AddOnLen int
19,@LFCR int --lengths of line feed carriage return
20,@DefinedLength bigint
21
22/* NOTE: Length of @SyscomText is 4000 to replace the length of
23** text column in syscomments.
24** lengths on @Line, #CommentText Text column and
25** value for @DefinedLength are all 255. These need to all have
26** the same values. 255 was selected in order for the max length
27** display using down level clients
28*/
29,@SyscomText nvarchar(max)
30,@Line nvarchar(max)
31
32select @DefinedLength = 4000
33select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
34 trailing blank spaces*/
35CREATE TABLE #CommentText
36(LineId int
37 ,Text nvarchar(4000) collate database_default)
38
39/*
40** Make sure the @objname is local to the current database.
41*/
42select @dbname = parsename(@objname,3)
43if @dbname is null
44 select @dbname = db_name()
45else if @dbname <> db_name()
46 begin
47 raiserror(15250,-1,-1)
48 return (1)
49 end
50
51/*
52** See if @objname exists.
53*/
54select @objid = object_id(@objname)
55if (@objid is null)
56 begin
57 raiserror(15009,-1,-1,@objname,@dbname)
58 return (1)
59 end
60
61-- If second parameter was given.
62if ( @columnname is not null)
63 begin
64 -- Check if it is a table
65 if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0
66 begin
67 raiserror(15218,-1,-1,@objname)
68 return(1)
69 end
70 -- check if it is a correct column name
71 if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0)
72 begin
73 raiserror(15645,-1,-1,@columnname)
74 return(1)
75 end
76 if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)
77 begin
78 raiserror(15646,-1,-1,@columnname)
79 return(1)
80 end
81
82 declare ms_crs_syscom CURSOR LOCAL
83 FOR select text from syscomments where id = @objid and encrypted = 0 and number =
84 (select column_id from sys.columns where name = @columnname and object_id = @objid)
85 order by number,colid
86 FOR READ ONLY
87
88 end
89else if @objid < 0 -- Handle system-objects
90 begin
91 -- Check count of rows with text data
92 if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0
93 begin
94 raiserror(15197,-1,-1,@objname)
95 return (1)
96 end
97
98 declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid
99 ORDER BY number, colid FOR READ ONLY
100 end
101else
102 begin
103 /*
104 ** Find out how many lines of text are coming back,
105 ** and return if there are none.
106 */
107 if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
108 and o.id = c.id and o.id = @objid) = 0
109 begin
110 raiserror(15197,-1,-1,@objname)
111 return (1)
112 end
113
114 if (select count(*) from syscomments where id = @objid and encrypted = 0) = 0
115 begin
116 raiserror(15471,-1,-1,@objname)
117 return (0)
118 end
119
120 declare ms_crs_syscom CURSOR LOCAL
121 FOR select text from syscomments where id = @objid and encrypted = 0
122 ORDER BY number, colid
123 FOR READ ONLY
124
125 end
126
127/*
128** else get the text.
129*/
130select @LFCR = 2
131select @LineId = 1
132
133OPEN ms_crs_syscom
134
135FETCH NEXT from ms_crs_syscom into @SyscomText
136
137WHILE @@fetch_status >= 0
138begin
139
140 select @BasePos = 1
141 select @CurrentPos = 1
142 select @TextLength = LEN(@SyscomText)
143
144 WHILE @CurrentPos != 0
145 begin
146 --Looking for end of line followed by carriage return
147 select @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
148
149 --If carriage return found
150 IF @CurrentPos != 0
151 begin
152 /*If new value for @Lines length will be > then the
153 **set length then insert current contents of @line
154 **and proceed.
155 */
156 while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
157 begin
158 select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
159 INSERT #CommentText VALUES
160 ( @LineId,
161 isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
162 select @Line = NULL, @LineId = @LineId + 1,
163 @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
164 end
165 select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
166 select @BasePos = @CurrentPos+2
167 INSERT #CommentText VALUES( @LineId, @Line )
168 select @LineId = @LineId + 1
169 select @Line = NULL
170 end
171 else
172 --else carriage return not found
173 begin
174 IF @BasePos <= @TextLength
175 begin
176 /*If new value for @Lines length will be > then the
177 **defined length
178 */
179 while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
180 begin
181 select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
182 INSERT #CommentText VALUES
183 ( @LineId,
184 isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
185 select @Line = NULL, @LineId = @LineId + 1,
186 @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
187 end
188 select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
189 if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
190 begin
191 select @Line = @Line + ' ', @BlankSpaceAdded = 1
192 end
193 end
194 end
195 end
196
197 FETCH NEXT from ms_crs_syscom into @SyscomText
198end
199
200IF @Line is NOT NULL
201 INSERT #CommentText VALUES( @LineId, @Line )
202
203select Text from #CommentText order by LineId
204
205CLOSE ms_crs_syscom
206DEALLOCATE ms_crs_syscom
207
208DROP TABLE #CommentText
209
210return (0) -- sp_helptext