How to get the whole line in matching word

Question

Post reply

How to get the whole line in matching word

yuvipoy

SSChampion

Points: 10469
More actions
December 19, 2011 at 10:26 pm

#248402

TableA is having columns as Employee_id,First_name,Last_name
TableB is having columns as Emp_id,F_name,L_name
in some stored procedures it is set as Emp_id= A.Employee_id ,F_name= LTRIM(A.First_name), L_name= LTRIM(A.Last_name)
i need to find the SPs which is having Employee_id or emp_id
but my input will be Employee_id alone ,it need to take Emp_id and Employee_id as both input and start new search
Here i have said only 3 columns for example but there are nearly 1200 columns like this,i need to find all of them in similar manner
I have used
declare @SearchWord varchar(1000)
declare @SearchWord1 varchar(1000)
SELECT @SearchWord='Employee_id'
Select name from sys.sysobjects where Xtype='p' and Id in(
Select id from Sys.syscomments where text like '%' + @SearchWord + '%')
@SearchWord1 need to capture emp_id
I have found that we can use this can be used to get @SearchWord1
declare @SearchWord varchar(1000)
Select @SearchWord ='Emp_id= A.Employee_id'
Select @SearchWord=Reverse(@SearchWord)
Select @SearchWord1=(Substring(Reverse(@SearchWord) ,0,Charindex('=',Reverse(@SearchWord) )))
Select @SearchWord1
but how to get this in a stored procedure.It means that it needs to get the whole line and set to @searchword1
Since it is having nearly 1200 columns it is hard to find and other way is there.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

yuvipoy SSChampion Points: 10469 More actions · Answer 1

Hi any one got the solution :w00t: still i am unable to get.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 2

You posted your question yesterday but didn't get any response. Would you like to know why?

We expect some information from you to help you better. It’s Explained in the article below.

Forum Etiquette: How to post data/code on a forum to get the best help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

yuvipoy SSChampion Points: 10469 More actions · Answer 3

Hi,

What information you need?

where did you need me to explain?.

i have given conditions i think you where not able to read my post correctly.Other peoples who have read the post might have understand but they might not know how to proceed with the logic as i am struggling.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

yuvipoy (12/21/2011)
Hi,
What information you need?
where did you need me to explain?.
i have given conditions i think you where not able to read my post correctly.Other peoples who have read the post might have understand but they might not know how to proceed with the logic as i am struggling.

If you actually read that article it will tell you that in order for the volunteers on this board to help we prefer to have ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. That way we can spend our volunteer time working on your solution instead of spending lots of time setting up the problem in our environment. There are lots of people on here willing to help you write tested, fast and accurate sql but not too many are willing to parse through lots of text and vague explanations to then spend time building your test data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

Once you know which procedure has your search word, you can dump that into a table that has each line of text for the procedure. Then search the table for your search word.

Here is an example of capturing the text for a procedure:

CREATE TABLE #Temp

( SomeTextNVARCHAR(1000) )

DECLARE

@SProc sysname

, @SQL NVARCHAR(1000)

SET @SProc = 'uspPrintError' -- Just an example

SET @SQL = 'EXEC sp_helptext ''' + @SProc + ''''

Todd Fifield

INSERT INTO #Temp

EXEC (@SQL)

SELECT * FROM #Temp

yuvipoy SSChampion Points: 10469 More actions · Answer 6

tfifield (12/21/2011)
Once you know which procedure has your search word, you can dump that into a table that has each line of text for the procedure. Then search the table for your search word.
Here is an example of capturing the text for a procedure:
CREATE TABLE #Temp
( SomeTextNVARCHAR(1000) )
DECLARE
@SProc sysname
, @SQL NVARCHAR(1000)
SET @SProc = 'uspPrintError' -- Just an example
SET @SQL = 'EXEC sp_helptext ''' + @SProc + ''''
Todd Fifield
INSERT INTO #Temp
EXEC (@SQL)
SELECT * FROM #Temp

Thanks a Lot tfifield this is what i am looking for thanks a many, you have understand my points.

Superb,u have done a excellent work it helped to lot.i dont even think of inserting into Temp table and do,but now i am clear.