SQL Server provides different metadata views that can help developers in their work, but sometimes developers are not up to date with the latest system tables and views changes.
In this Q&A we provide the query to search for the specific text in the stored procedures, views or functions. This is the easiest way to search for the string in objects definitions:
SELECT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules
WHERE [definition] LIKE '%my_search_string%'
Another way is using syscomments system table which was used in older versions of SQL Server:
SELECT OBJECT_NAME(ID) FROM sys.syscomments
WHERE [text] LIKE '%my_search_string%'
Also, you can use OBJECT_DEFINITION metadata function:
SELECT name, type_desc FROM sys.all_objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%my_search_string%'
There was a user question on how to shrink the log files with dynamic SQL (without hard coding databases names). The script is provided below. But first – make sure that you size the log files properly (large enough to grow) and make sure that you backup regularly the log files (or use simple recovery mode). And do not truncate the logs, shrink them just to the size you need. So, if my log files do not usually grow larger than 4 GB I will use the following script:
SET NOCOUNT ON
CREATE table #tran_log_space_usage(
, log_size_mb float
, log_space_used float
, status int
insert into #tran_log_space_usage
exec(‘DBCC SQLPERF ( LOGSPACE )’) ;
delete from #tran_log_space_usage where database_name = ‘Exclude_DB’
EXEC dbo.sp_msforeachdb ‘
DECLARE @log_file VARCHAR(150)
IF (SELECT DB_ID(”?”)) > 4 and (SELECT DATABASEPROPERTY(”?”, ”IsOffline”)) <>1
IF (select log_size_mb as LogSizeMB
where database_name = ”?”) > 4048
select @log_file = name
from [?].sys.database_files where file_id = 2
DBCC SHRINKFILE (@log_file , 4048)
DROP table #tran_log_space_usage
You can add exceptions for the databases as well (see ‘Exclude_DB’ in script).