Searching Hard Coded Values

Searching Hard Coded Values from Entire Database-Objects is very Interesting Task for me. My last Blog (Search Dynamic Values) was for Dynamic Values from Tables. Today I want to show how I am searching Hard Coded values (E-mail Ids and Mobile numbers) from all Procedures or Views from our Database.We should avoid the Hard codes in objects.

To manages Hard codes in objects  i was simply Converting all procedures and Views in script and after that using find -replace i was managing changes...It was very time consuming and bothering work form me... Below Query help and save my lot of time for learning extras.....

       

declare @text varchar(100)

declare @dbname varchar(100) 

  

  set @text = 'sumank'

  set @dbname = 'masterdatabase'

if @dbname is null

begin

--enumerate all databases.

DECLARE #db CURSOR FOR Select Name from master..sysdatabases

declare @c_dbname varchar(64)

  

OPEN #db FETCH #db INTO @c_dbname

while @@FETCH_STATUS <> -1 --and @MyCount < 500

   begin

     execute find_text_in_sp @text, @c_dbname

     FETCH #db INTO @c_dbname

   end 

  CLOSE #db DEALLOCATE #db

 end --if @dbname is null

else

 begin --@dbname is not null

  declare @sql varchar(250)

  --create the find like command

  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '

  select @sql = @sql + ' from '+@dbname+'.sys.sql_modules m '

  select @sql = @sql + ' inner join '+@dbname+'..sysobjects o on m.object_id=o.id'

  select @sql = @sql + ' where [definition] like ''%'+@text+'%'''

  execute (@sql)

 end --@dbname is not null

Result :-

@sumanjha