none
Find all the database objects which are using hard coded text RRS feed

  • Question

  • Is there any reference query in SQL which can be used to get all the objects in the database which is using hard coded texts.

    In order to make the code more robust and flexible for translations in future ,I need to find all the Stored Procs/functions/Views which is using any hard coded strings except blanks ''. As there are too many its hard to open each and check.


    Best Regards, Pragati


    Wednesday, September 18, 2019 3:58 PM

Answers

  • The following will find every function, stored proc or view that has any quote marks (') unless that quote mark is immediately followed by 0 or more spaces followed by another quote mark.  It will also find the object if it has 3 or more quote marks in a row (like '''') since that is not a blank string. 

    select s.name as SchemaName, o.name As ObjectName, o.type_desc, m.definition 
    from sys.schemas s
    inner join sys.objects o on s.schema_id = o.schema_id
    inner join sys.sql_modules m on o.object_id = m.object_id
    Where Replace(Replace(m.definition, '''''', ''), ' ', '') Like '%''_%''%'
     Or m.definition Like '%''''''%';

    Note that this code cannot tell the difference between comments and actual code, so if you have comments in your code of the object and the comment has a quote mark, this code will return that object.

    Tom


    Wednesday, September 18, 2019 6:10 PM
  • Hi pragati sharma,

    Hope the below script,query and with example that will help you a lot!

    /*
    create table test
    (
    id int not null identity(1,1),
    samples varchar(100)
    );
    
    insert into test (samples)
    values ('aaa'),('bbb'),('ccc');
    */
    
    --	select * from test;
    
    --sp 1--> having hard coded value (example aaa is hardcoded)
    create or alter procedure test1_sp
    as 
    begin
    select id,samples from test where samples='aaa';
    end;
    go
    --sp 2--> not having hardcoded values
    create or alter procedure test2_sp
    as 
    begin
    select id,samples from test;
    end;
    go
    --function --> having hard coded value (example aaa is hardcoded)
    create or alter function test_fn()
    returns int
    AS 
    BEGIN
    	DECLARE @id int;
    	SELECT @id=id from test where samples='aaa';
    	RETURN @id ;
    END;
    go
    --view --> having hard coded value (example aaa is hardcoded)
    create or alter view test_view as
    select id,samples from test where samples='aaa';
    go
    
    --Expected Result query
    ​select ​
    distinct o.type,o.type_desc,object_name(c.id)​ as [object_name]
    from ​
    sys.syscomments ​c join sys.objects o on c.id=o.object_id
    where ​
    c.text like '%aaa%'​; --give your hardcoded value here
    go
    
    

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Marked as answer by Pragati Sharma Friday, September 20, 2019 9:48 AM
    Wednesday, September 18, 2019 6:15 PM

All replies

  • The following will find every function, stored proc or view that has any quote marks (') unless that quote mark is immediately followed by 0 or more spaces followed by another quote mark.  It will also find the object if it has 3 or more quote marks in a row (like '''') since that is not a blank string. 

    select s.name as SchemaName, o.name As ObjectName, o.type_desc, m.definition 
    from sys.schemas s
    inner join sys.objects o on s.schema_id = o.schema_id
    inner join sys.sql_modules m on o.object_id = m.object_id
    Where Replace(Replace(m.definition, '''''', ''), ' ', '') Like '%''_%''%'
     Or m.definition Like '%''''''%';

    Note that this code cannot tell the difference between comments and actual code, so if you have comments in your code of the object and the comment has a quote mark, this code will return that object.

    Tom


    Wednesday, September 18, 2019 6:10 PM
  • Hi pragati sharma,

    Hope the below script,query and with example that will help you a lot!

    /*
    create table test
    (
    id int not null identity(1,1),
    samples varchar(100)
    );
    
    insert into test (samples)
    values ('aaa'),('bbb'),('ccc');
    */
    
    --	select * from test;
    
    --sp 1--> having hard coded value (example aaa is hardcoded)
    create or alter procedure test1_sp
    as 
    begin
    select id,samples from test where samples='aaa';
    end;
    go
    --sp 2--> not having hardcoded values
    create or alter procedure test2_sp
    as 
    begin
    select id,samples from test;
    end;
    go
    --function --> having hard coded value (example aaa is hardcoded)
    create or alter function test_fn()
    returns int
    AS 
    BEGIN
    	DECLARE @id int;
    	SELECT @id=id from test where samples='aaa';
    	RETURN @id ;
    END;
    go
    --view --> having hard coded value (example aaa is hardcoded)
    create or alter view test_view as
    select id,samples from test where samples='aaa';
    go
    
    --Expected Result query
    ​select ​
    distinct o.type,o.type_desc,object_name(c.id)​ as [object_name]
    from ​
    sys.syscomments ​c join sys.objects o on c.id=o.object_id
    where ​
    c.text like '%aaa%'​; --give your hardcoded value here
    go
    
    

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Marked as answer by Pragati Sharma Friday, September 20, 2019 9:48 AM
    Wednesday, September 18, 2019 6:15 PM