Posts

Showing posts from May, 2005

Searching Stored Procedures Source Code

I found this article while searching for some "text" in my own stored procedures, (the article link is available at the bottom.) --------------- October 6, 2000 Searching Stored Procedures Source Code By Alan Enderby Want to find that stored procedure that deletes those stock items. This simple (& crude) script will scan syscomments for a given string and display the stored procedure and the section of code. if exists (select * from sysobjects where id = object_id('dbo.sp_findsp') and sysstat & 0xf = 4) drop procedure dbo.sp_findsp GO create proc sp_findsp @s varchar(255) as DECLARE @msg varchar(255) ,@ul varchar(255) select @s='%' + @s + '%' select 'SP Name'=upper(o.name), Seq=colid ,'SP Line'=substring(text,patindex(@s,text)-5, 30) from syscomments c , sysobjects o where o.id=c.id and patindex(@s,text) > 0 order by name SELECT @msg='* Stored procedures containing string "' + @s +

the Doppelganger

Data scrubbing is a fact of database life. Whenever you import and normalize data you do run the risk of duplicate data imports as well. This can be especially true if your source database had few restrictions on preventing duplicate data entry. In one particular table I had many rows with duplicate data down to the entrytime field; everything was identical except for the PKID. I tried the typical Cursor script found in various websites, but that would Yield an estimate of 14hrs while the test server processed all 16k rows! I could not very well do that to the production server, on my quest I tried several while loops and eventually came up with this select statement which for my situation (2 duplicate rows for 1 real row of data) First thing I did was dump out all the duplicate rows PK's and FK's into a temp table called #Dup, I hit upon this select statment and I sucessfully narrowed down 16k rows down to 8k of unique data, and was able to delete the duplicate FK's b