Search And Replace Within Store Procedures

Nov 29, 2004

I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.





Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?

View 1 Replies


ADVERTISEMENT

Search & Replace In Multiple Stored Procedures

Aug 31, 2007

Hi all,

Does anyone know how to do the search and replace in multiple stored procedures using SQL 2005 Management Studio?
Any help will be appreciated.

Thanks!

View 3 Replies View Related

Search & Replace

Aug 4, 2006

i like to write a code that street_name field look up in table one for 'road' and replace it for 'rd'.
any approaches ?

table1
abr description
---- ------------
ln lane
rd road
ave avenue


table2
street_name
-------------
apple road

View 20 Replies View Related

Search And Replace

Feb 1, 2008

i have data in a table which is in format #12345;#22211;#12112; and so on... its a long string.

i want to replace only the numbers using nchar() function.
in above example i want to replace 12345, 22211, 12112 with nchar(12345), nchar(22211), nchar(12112).

i have managed to do this using loop and substring function. but now i have performance issue.

63K records will take many hours to update. i have to do following steps:
1. read record from table.
2. replace numbers with nchar() function.
3. update the same table with new text.

i have to do this using a stored procedure.

i am looking for some quick way to do this. any idea?

View 1 Replies View Related

Search And Replace?

Aug 30, 2007

I have a notes table in which I have 10,000 rows. Unfortunately some of the notes are compressed together as one long string. I can fix the issue if I can do a search and replace on ';' and replace with '; ' is there a way to do this?

View 3 Replies View Related

Search And Replace (sorta)

Jul 6, 2007

I have a question about the best method of going about doing this.

I have a records table which stores visitor information from IISlogs (dbo.records)

I have added IP tables to try and resolve where the visitors are from. They are labeled 'ip4_##' where ## is the first section of the ip.. so if my IP was 24.150.66.80 I would need to look in table ip4_24 and inside there look for 150 in column B and then 66 in column C. From that it will say which country is attached, that way I can update the countrycode in the dbo.records.

I am not sure how to do this search, so any ideas would be great.

Thanks in advance



* Table structre for the ip_## tables are
COLUMN 'b' int (represents the 2nd section of an IP)
COLUMN 'c' int (represents the third section of an IP)
COLUMN 'country' int

View 2 Replies View Related

Search All Tables And Replace

Jul 23, 2005

I'm looking for a stored procedure (or query) to search an entiredatabase for a specific string value and replace it with another. I'msure I saw an SP for this a while back by someone, but cannot find itagain. The SP took the search string and replace string as parametersand did the rest. Any ideas where I can find this ?Bear in mind, the idea is that this can be re-used and run on anydatabase, so it would have to find all tables and search through those.TaRyan

View 2 Replies View Related

SQL Server Search-and-replace Program

Apr 21, 2006

I found a search-and-replace program for SQL server that works GREAT, but I have a list of about 200 words to search for, with corresponding replacements. Rather than editing the code below for *each* word and running it 200 separate times, I'd like to iterate through the list, but my MS-SQL programming skills are...light (to say the least). Anyone have any ideas how I can create a list or hash-type variable and use the code to loop through and do the replacements all at once?

/*
*
* Search & Replace
*
* Use Ctrl+Shift+M to replace template values
*
*/

set xact_abort on
begin tran

declare @otxt varchar(1000)
set @otxt = '<string1, text, text to be replaced>'

declare @ntxt varchar(1000)
set @ntxt = '<string2, text, replacing text>'

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select
id,
textptr(<field_name, sysname, target text field>),
charindex(@otxt, <field_name, sysname, target text field>)-1
from
<table_name, sysname, target table>
where
<field_name, sysname, target text field>
like
'%' + @otxt +'%'

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)

updatetext <table_name, sysname, target table> .<field_name, sysname, target text field> @ptr @pos @txtlen @ntxt

fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

commit tran

View 1 Replies View Related

How To Search For And Replace A Value In All Tables Using A Sql Script.

Mar 15, 2007

Hello Everyone,

Is there a way to search all the tables in a database for a value that is found in a specific column? Another problem is that although this specific column will be found in most of the tables in the database, it has a different two or three digit prefix in the column name for each table.

I think the logic will go something like this...

As the script jumps from table to table, it should first look for a column whose name ends with ***ITM. If it does not find a column with ***ITM it should move on to the next table. If it does, then search the ***ITM column for my value. If it does not find my value, then move to the next table. But if it does, change my value to a new value.

Any help will be greatly appreciated.

Thank you all...

View 3 Replies View Related

REPLACE CARRIAGE RETURN While Search

Oct 26, 2007



Hi All,
I am experiencing problem to select text wich has carriage return in my search functionality.

I have two tables called @searchwordTable and @DataTable.

@searchWordTable will have search criteria words(data type is varchar) and @DataTable will titles(data type is ntext) need to be searched. Some of titles have carriage return,line feed and tab characters. I am preseting here script to reproduce my problem.


DECLARE @searchwordTable TABLE

(

searchword VARCHAR(MAX)

)

INSERT INTO @searchwordTable (searchword) VALUES('carriage long description')


DECLARE @DataTable TABLE

(

title ntext

)

INSERT INTO @DataTable (title) values('carriage long description'+char(13)+char(10)+'carriagelong')


SELECT * FROM @DataTable dTable,@searchwordTable srcWrdTable

WHERE '% ' + REPLACE(CAST(dTable.title AS VARCHAR(MAX)),char(13),'') + ' %' like '% ' + srcWrdTable.searchword + ' %'


I am expecting the above select statement should select title from @DataTable but not getting .I am not understanding what is going wrong with above select.

Thanks in Advance.

View 2 Replies View Related

Search Replace Across Multiple Queries

Oct 4, 2007



Hey guys,

In my DB i have hundreds on queries setup for all different reporting purposes..

We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.

Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.

I am dreading the idea of opening each individual queries and checking if it exists..

If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.

Thanks
Scotty

View 3 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 22, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 1 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 26, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 4 Replies View Related

Transformation Editor Or SQL Eitor. How To Search And Replace?

Mar 8, 2007

Hi SSIS Gurus,

Coild anybody halp me, how to achive search and replace in the Expressions (Transformation Editor) or in SQL Query box?

If this functionality isn't presented, what is preffered woraround to achieve it?

I have a lot of transformations (about 100) in the derived column task and its too difficult to find transformation what i need.

View 1 Replies View Related

Simple Text Processing E.g. Regex Search And Replace

Aug 8, 2006

I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).

Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?

Thanks!

View 13 Replies View Related

This Stored Procedure Can Be Used To Search And Replace Substring In The Char, Nchar,

Jan 9, 2006

#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):

EXEC replace_substring @search_value = 'John', @replace_value = 'Bill'

View 2 Replies View Related

SQL Server 2008 :: Replace Tablename In Multiple Stored Procedures?

Mar 3, 2015

I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.

-- set "Result to Text" mode by pressing Ctrl+T

SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'

[code].....

View 0 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

Store Procedures

Jan 27, 2004

hi everyone :)

I've got a store procedure(sp) on the database side ( MS SQL 2000 )

and want to access the sp via ASP.net

so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.

I've tested the SP and it does work fine

can you just give me a template of how to access the sp from ASP.net


mant thanks indeed

M

View 2 Replies View Related

Store Procedures For DBA

Mar 16, 2008

Can anyone plz tell me what kind of store procedures DBA's needs to write or DBA needs to know?

View 2 Replies View Related

Store Procedures

Jan 11, 2006

Hi:Does anyone know if IBM (or any other IT Software company) offers storeprocedure classes anywhere in the country?ThanksMarc

View 2 Replies View Related

Store Procedures Return

Apr 28, 2000

Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.

Thank you

View 1 Replies View Related

TSQL And ASP - Store Procedures Help!!!!!!

Dec 1, 2000

All,
I have beating my head over this and have asked questions to clear this up in small bits.

In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:


--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'

'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath

howmanyfields=SysMainQuery.fields.count -1

response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue="&nbsp;"
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"

' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>


The stored procedure takes the input an returns the sql statement:

Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int

Set @wheretmp = ''
Set @tmplen = 0

if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''

If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp

print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go


The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....

Has anyone ever run into this?
Thanks
~Lee

View 2 Replies View Related

SQL 6.5 Security For Store Procedures

Sep 30, 1999

How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.

TIA for all you responses

View 1 Replies View Related

Store Procedures &&amp; Functions

Feb 10, 2006

hi,

what is the different between store procedure and function?

when it is right to use sp and when functions?

thanks in advanced

View 3 Replies View Related

SQL Search :: How To Find What Are All Columns Used In A Store Procedure

Nov 23, 2015

I would like to know all the columns used in a store procedures.I have used dynamic sql to create these procedures .

View 4 Replies View Related

Job Site Searching Resumes How To Store And Search?

May 29, 2007

I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on?

SQL 2005

View 5 Replies View Related

Speeding Up Store Procedures Using EXEC?

May 26, 2004

Hello, can anyone offer any advice on this problem related to store procedures.

The following 2 chunks of SQL illustrate the problem

--1
declare @lsFilt varchar(16)
select @lsFilt = 'fil%ter'
select * from sysobjects where name like @lsFilt

--2
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
exec (@lsQuery)

When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.


Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of

create proc SP2Style
@psFilter varchar(16)
AS
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
exec (@lsQuery)
GO

instead of

create proc SP1Style
@psFilter varchar(16)
AS
select * from sysobjects where name like @psFilter
GO

Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?

thanks

Paul

View 2 Replies View Related

How To Give The Grant On Store Procedures

Dec 19, 2007

hello i am new to sql server 2000.

i am one of the user of database and in that user i created store procedures.

now i want to create another user and give that user to permission of excute all my proceudres and i also give him privillages such a way that user can also modify my procedures...

so can u assist me on that...

thanks in advance...

View 5 Replies View Related

Can't Import Views And Store Procedures

Aug 6, 2007

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong
awong@virginiadare.com

View 3 Replies View Related

Temp Table In Store Procedures

Jan 25, 2008

Can any one please tell me where i am going wrong..




Code Snippet
create proc SP_PercentageRMU
as
SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty'
,RAW_MATERIAL
into #TZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]

group by RAW_MATERIAL
GO
select [Usage Qty],(case when r.raw_material = z.raw_material
then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8))
else 0
end) as '%UsageQty'
,r.[PRODL]

,r.RAW_MATERIAL
,r.[GRADECODE]
into #PZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r
inner join #TZMelt_Pound z on r.raw_material = z.raw_material

drop table #TZMelt_Pound
go






error

Msg 208, Level 16, State 0, Line 2

Invalid object name '#TZMelt_Pound'.

View 6 Replies View Related

Calling DB2 Store Procedures From SQL Server 2000

Feb 5, 2004

I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.

Has anyone come across doing this and how they did it?

THanks for the help....

View 4 Replies View Related

SQL 2012 :: Find Out Which Store Procedures Fired

Apr 16, 2014

Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved