Hi,

I need to know if there is some query which would pull up all the SPs where the hardcoded values have been used from a particular DB.

For example if we have a Database as sales and we have some 20 SPs under this database out of these 20 SP we have used hardcoded values in 5 SPs. So i need to know the query which would pull up these 5 SPs having a hardcoded value.

Thanks,

Varsha

What do you mean by hardcoded values?
Post the script of the procedure that has hardcoded values

from lookupproduct

where ShortName in ('WC-Inv', 'WC-GB') )

ORDER BY dbo.SalesClient.Name;

END

IF (@Segment = 'WCM') AND (@TMEKey in (64,65,91))

 --This is just a part of procedure where we have used hard coding. This part is hard coded (@TMEKey in (64,65,91)). These kind of hard coding has been used in several SPs and if anything changes then we have to go to all procedures and update it. hence we thought that instead of calling these values directly we will call a common function which will point to these values. In that case if there is an update to this we can just go to this common function and update it instead of updating so many SPs. let me know if you want a complete script because it really long and i dont think i can attach it here..

select object_name(id) as proc_names from syscomments
where text like '%''[a-z0-9]''%'
order by 1

Hello,

If you are under SQL Server 2005, why continue to use sys.syscomments or INFORMATION_SCHEMA.ROUTINES instead of sys.sql_modules ?

sys.sql_modules is not limited to 4000 characters !

If I am searching within user defined routines (Procedurs, fucntions and views), I will go with sys.sql_modules or INFORMATION_SCHEMA.Rountines as they will eliminate system created views. But check constraints (Which normally have hard coded values) are not covered with there. So it depends of what do you really want to search.
More than that, it is also depends on your practice.Many of us haev used syscomments for a long time, and it is hard to change immediately. :)

elsuket:

Hello,

 

If you are under SQL Server 2005, why continue to use sys.syscomments or INFORMATION_SCHEMA.ROUTINES instead of sys.sql_modules ?

sys.sql_modules is not limited to 4000 characters !

Yes you can. I forgot to notice that this is posted in 2005 forum