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 :-
Nice..
ReplyDeleteThank You Manjeet G...
ReplyDelete