· 8 years ago · Jun 28, 2018, 10:56 AM
1SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
2 FROM INFORMATION_SCHEMA.ROUTINES
3 WHERE ROUTINE_DEFINITION LIKE '%tblVacationAllocationItem%'
4 AND ROUTINE_TYPE='PROCEDURE'
5 ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME
6
7SET ANSI_NULLS ON
8GO
9SET QUOTED_IDENTIFIER OFF
10GO
11
12/*********************************************************************
13* Stored procedure sp_grep
14* SQL Server: Microsoft SQL Server 6.0, 4.21 for Windows NT,
15* Microsoft SQL Server 4.2 for OS/2.
16* Author: Andrew Zanevsky, AZ Databases, Inc.
17* Version/Date: Version 1.1, October 26, 1995
18* Description: Searches syscomments table in the current database
19* for occurences of a combination of strings.
20* Correclty handles cases when a substring begins in
21* one row of syscomments and continues in the next.
22* Parameters: - @parameter describes the search:
23* string1 {operation1 string2} {operation2 string 3} ...
24* where - stringN is a string of characters enclosed in
25* curly brackets not longer than 80 characters.
26* Brackets may be omitted if stringN does not
27* contain spaces or characters: +,-,&;
28* - operationN is one of the characters: +,-,&.
29* Parameter is interpreted as follows:
30* 1.Compose the list of all objects where string1 occurs.
31* 2.If there is no more operations in the parameter,
32* then display the list and stop. Otherwise continue.
33* 3.If the next operation is + then add to the list all
34* objects where the next string occurs;
35* else if the next operation is - then delete from the
36* list all objects where the next string occurs;
37* else if the next operation is & then delete from the
38* list all objects where the next string does not
39* occur (leave in the list only those objects where
40* the next string occurs);
41* 4.Goto step 2.
42* Parameter may be up to 255 characters long, and may not
43* contain <CarriageReturn> or <LineFeed> characters.
44* Please note that operations are applied in the order
45* they are used in the parameter string (left to right).
46* There is no other priority of executing them. Every
47* operation is applied to the list combined as a result
48* of all previous operations.
49* Number of spaces between words of a string matters in a
50* search (e.g. "select *" is not equal to "select *").
51* Short or frequently used strings (such as "select") may
52* produce a long result set.
53*
54* - @case: i = insensitive / s = sensitive (default)
55* Insensitive search is performed regardless of this parameter
56* if SQL Server is set up with case insensitive sort order.
57*
58* Examples: sp_grep employee
59* list all objects where string 'employee' occurs;
60* sp_grep employee, i
61* list all objects where string 'employee' occurs in
62* any case (upper, lower, or mixed), such as
63* 'EMPLOYEE', 'Employee', 'employee', etc.;
64* sp_grep 'employee&salary+department-trigger'
65* list all objects where either both strings 'employee'
66* and 'salary' occur or string 'department' occurs, and
67* string 'trigger' does not occur;
68* sp_grep '{select FirstName + LastName}'
69* list all objects where string
70* "select FirstName + LastName" occurs;
71* sp_grep '{create table}-{drop table}'
72* list all objects where tables are created and not
73* dropped.
74*
75**********************************************************************/
76
77-- sp_grep v1.0 03/16/1995, v1.1 10/26/1995
78-- Author: Andrew Zanevsky, AZ Databases, Inc.
79-- E-mail: zanevsky@azdatabases.com
80ALTER proc [dbo].[sp_grep] @parameter varchar(255) = null, @case char(1) = 's'
81as
82
83declare @str_no tinyint,
84 @msg_str_no varchar(3),
85 @operation char(1),
86 @string varchar(80),
87 @oper_pos smallint,
88 @context varchar(255),
89 @i tinyint,
90 @longest tinyint,
91 @msg varchar(255)
92
93if @parameter is null /* provide instructions */
94begin
95 print 'Execute sp_grep "{string1}operation1{string2}operation2{string3}...", [case]'
96 print '- stringN is a string of characters up to 80 characters long, '
97 print ' enclosed in curly brackets. Brackets may be omitted if stringN '
98 print ' does not contain leading and trailing spaces or characters: +,-,&.'
99 print '- operationN is one of the characters: +,-,&. Interpreted as or,minus,and.'
100 print ' Operations are executed from left to right with no priorities.'
101 print '- case: specify "i" for case insensitive comparison.'
102 print 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
103 print ' will search for all objects that have an occurence of string "alpha"'
104 print ' or string "beta gamma", do not have string "delta", '
105 print ' and have string "+++".'
106 return
107end
108
109/* Check for <CarriageReturn> or <LineFeed> characters */
110if charindex( char(10), @parameter ) > 0 or charindex( char(13), @parameter ) > 0
111begin
112 print 'Parameter string may not contain <CarriageReturn> or <LineFeed> characters.'
113 return
114end
115
116if lower( @case ) = 'i'
117 select @parameter = lower( ltrim( rtrim( @parameter ) ) )
118else
119 select @parameter = ltrim( rtrim( @parameter ) )
120
121create table #search ( str_no tinyint, operation char(1), string varchar(80), last_obj int )
122create table #found_objects ( id int, str_no tinyint )
123create table #result ( id int )
124
125/* Parse the parameter string */
126select @str_no = 0
127while datalength( @parameter ) > 0
128begin
129 /* Get operation */
130 select @str_no = @str_no + 1, @msg_str_no = rtrim( convert( char(3), @str_no + 1 ) )
131 if @str_no = 1
132 select @operation = '+'
133 else
134 begin
135 if substring( @parameter, 1, 1 ) in ( '+', '-', '&' )
136 select @operation = substring( @parameter, 1, 1 ),
137 @parameter = ltrim( right( @parameter, datalength( @parameter ) - 1 ) )
138 else
139 begin
140 select @context = rtrim( substring(
141 @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
142 select @msg = 'Incorrect or missing operation sign before "' + @context + '".'
143 print @msg
144 select @msg = 'Search string ' + @msg_str_no + '.'
145 print @msg
146 return
147 end
148 end
149
150 /* Get string */
151 if datalength( @parameter ) = 0
152 begin
153 print 'Missing search string at the end of the parameter.'
154 select @msg = 'Search string ' + @msg_str_no + '.'
155 print @msg
156 return
157 end
158 if substring( @parameter, 1, 1 ) = '{'
159 begin
160 if charindex( '}', @parameter ) = 0
161 begin
162 select @context = rtrim( substring(
163 @parameter + space( 255 - datalength( @parameter) ), 1, 200 ) )
164 select @msg = 'Bracket not closed after "' + @context + '".'
165 print @msg
166 select @msg = 'Search string ' + @msg_str_no + '.'
167 print @msg
168 return
169 end
170 if charindex( '}', @parameter ) > 82
171 begin
172 select @context = rtrim( substring(
173 @parameter + space( 255 - datalength( @parameter) ), 2, 20 ) )
174 select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
175 print @msg
176 select @msg = 'String begins with "' + @context + '".'
177 print @msg
178 return
179 end
180 select @string = substring( @parameter, 2, charindex( '}', @parameter ) - 2 ),
181 @parameter = ltrim( right( @parameter,
182 datalength( @parameter ) - charindex( '}', @parameter ) ) )
183 end
184 else
185 begin
186 /* Find the first operation sign */
187 select @oper_pos = datalength( @parameter ) + 1
188 if charindex( '+', @parameter ) between 1 and @oper_pos
189 select @oper_pos = charindex( '+', @parameter )
190 if charindex( '-', @parameter ) between 1 and @oper_pos
191 select @oper_pos = charindex( '-', @parameter )
192 if charindex( '&', @parameter ) between 1 and @oper_pos
193 select @oper_pos = charindex( '&', @parameter )
194
195 if @oper_pos = 1
196 begin
197 select @context = rtrim( substring(
198 @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
199 select @msg = 'Search string ' + @msg_str_no +
200 ' is missing, before "' + @context + '".'
201 print @msg
202 return
203 end
204 if @oper_pos > 81
205 begin
206 select @context = rtrim( substring(
207 @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
208 select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
209 print @msg
210 select @msg = 'String begins with "' + @context + '".'
211 print @msg
212 return
213 end
214
215 select @string = substring( @parameter, 1, @oper_pos - 1 ),
216 @parameter = ltrim( right( @parameter,
217 datalength( @parameter ) - @oper_pos + 1 ) )
218 end
219 insert #search values ( @str_no, @operation, @string, 0 )
220
221end
222select @longest = max( datalength( string ) ) - 1
223from #search
224/* ------------------------------------------------------------------ */
225/* Search for strings */
226if @case = 'i'
227begin
228 insert #found_objects
229 select a.id, c.str_no
230 from syscomments a, #search c
231 where charindex( c.string, lower( a.text ) ) > 0
232
233 insert #found_objects
234 select a.id, c.str_no
235 from syscomments a, syscomments b, #search c
236 where a.id = b.id
237 and a.number = b.number
238 and a.colid + 1 = b.colid
239 and charindex( c.string,
240 lower( right( a.text, @longest ) +
241/* space( 255 - datalength( a.text ) ) +*/
242 substring( b.text, 1, @longest ) ) ) > 0
243end
244else
245begin
246 insert #found_objects
247 select a.id, c.str_no
248 from syscomments a, #search c
249 where charindex( c.string, a.text ) > 0
250
251 insert #found_objects
252 select a.id, c.str_no
253 from syscomments a, syscomments b, #search c
254 where a.id = b.id
255 and a.number = b.number
256 and a.colid + 1 = b.colid
257 and charindex( c.string,
258 right( a.text, @longest ) +
259/* space( 255 - datalength( a.text ) ) +*/
260 substring( b.text, 1, @longest ) ) > 0
261end
262/* ------------------------------------------------------------------ */
263select distinct str_no, id into #dist_objects from #found_objects
264create unique clustered index obj on #dist_objects ( str_no, id )
265
266/* Apply one operation at a time */
267select @i = 0
268while @i < @str_no
269begin
270 select @i = @i + 1
271 select @operation = operation from #search where str_no = @i
272
273 if @operation = '+'
274 insert #result
275 select id
276 from #dist_objects
277 where str_no = @i
278 else if @operation = '-'
279 delete #result
280 from #result a, #dist_objects b
281 where b.str_no = @i
282 and a.id = b.id
283 else if @operation = '&'
284 delete #result
285 where not exists
286 ( select 1
287 from #dist_objects b
288 where b.str_no = @i
289 and b.id = #result.id )
290end
291
292/* Select results */
293select distinct id into #dist_result from #result
294
295/* The following select has been borrowed from the sp_help
296** system stored procedure, and modified. */
297select Name = o.name,
298 /* Remove 'convert(char(15)' in the following line
299 ** if user names on your server are longer. */
300 Owner = convert( char(15), user_name(uid) ),
301 Object_type = substring(v.name + x.name, 1, 16)
302from #dist_result d,
303 sysobjects o,
304 master.dbo.spt_values v,
305 master.dbo.spt_values x
306where d.id = o.id
307/* SQL Server version 6.x uses 15, prior versions use 7 in expression below */
308and o.sysstat & ( 7 + 8 * sign( charindex( '6.', @@version ) ) ) = v.number
309and v.type = "O"
310and x.type = "R"
311and o.userstat & -32768 = x.number
312order by Object_type desc, Name asc
313
314select * from syscomments where text like '%tblVacationAllocationItem%'
315
316begin
317--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'
318--Declare the Table variable
319DECLARE @GeneratedStoredProcedures TABLE
320(
321 Number INT IDENTITY(1,1), --Auto incrementing Identity column
322 name VARCHAR(300) --The string value
323)
324
325--Decalre a variable to remember the position of the current delimiter
326DECLARE @CurrentDelimiterPositionVar INT
327declare @sqlCode varchar(max)
328--Decalre a variable to remember the number of rows in the table
329DECLARE @Count INT
330
331--Populate the TABLE variable using some logic
332INSERT INTO @GeneratedStoredProcedures SELECT name FROM sys.procedures where name like 'procGen_%'
333
334--Initialize the looper variable
335SET @CurrentDelimiterPositionVar = 1
336
337--Determine the number of rows in the Table
338SELECT @Count=max(Number) from @GeneratedStoredProcedures
339
340--A variable to hold the currently selected value from the table
341DECLARE @CurrentValue varchar(300);
342
343--Loop through until all row processing is done
344WHILE @CurrentDelimiterPositionVar <= @Count
345BEGIN
346 --Load current value from the Table
347 SELECT @CurrentValue = name FROM @GeneratedStoredProcedures WHERE Number = @CurrentDelimiterPositionVar
348 --Process the current value
349 --print @CurrentValue
350 set @sqlCode = 'drop procedure ' + @CurrentValue
351 print @sqlCode
352 --exec (@sqlCode)
353
354
355 --Increment loop counter
356 SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
357END
358
359end