It’s T-SQL Tuesday time again, and this month the host is Bert Wagner (b|t) whose topic of choice is “write about code you’ve written that you would hate to live without.”
There are so many different scripts I use every day it’s difficult to pick just one, but I find myself using the following script frequently. I don’t have this wrapped into a stored procedure because I’m often working with different clients who may or may not have permission to create a stored procedure, or they may have a change process to follow when any objects are to be added to the production database.
The script accepts 2 input variables:
@SearchPattern – This is going to be the text we’re trying to find amongst all the object definitions, columns, and jobs (see @incjobs variable).
@incjob – I do not search the agent jobs by default, but you can set this flag to 1 if you want. When this is set to 1, I look for a match in the job step along with the job name.
In the example below, I used the AdventureWorks2012 database and searched for ‘%hiredate%’
/*dan.clemens drcdba.com*/ DECLARE @SearchPattern NVARCHAR(128) ,@incjob bit SET @SearchPattern = '%%' SET @incjob = 0 --Set to 0 to ignore jobs /*Creates list of all tables and associated columns to do easy multi-column comparisons with a like.*/ DECLARE @tabletbl TABLE ( objectid int, tablename varchar(max), tabletype varchar(5), tablecolumns varchar(max) ) insert into @tabletbl select o.[object_id] ,o.name ,o.[type] ,STUFF(( SELECT ',' + c.Name As [text()] FROM sys.columns c WHERE c.[object_id] = o.[object_id] FOR XML PATH('') ), 1,1,'') As TableColumns from sys.objects o WHERE o.[type] IN ('S' --System Tables ,'U' --User Tables ,'V') --Views /*****************Table column creation end*****************/ DECLARE @jobtbl TABLE ( [schema] varchar(20), name varchar(max), [type] varchar(5), FullName varchar(max), [Source] nvarchar(max) ) IF @incjob = 1 BEGIN INSERT INTO @jobtbl SELECT 'job' as [Schema] ,name As Name ,'J' 'Type' ,name 'FullName' ,sjs.command 'Source' FROM msdb.dbo.sysjobs s inner join msdb.dbo.sysjobsteps sjs ON sjs.job_id = s.job_id where sjs.command like lower(@SearchPattern) or s.[name] like lower(@SearchPattern) END SELECT SCHEMA_NAME(o.schema_id) as [schema] ,o.[name] COLLATE DATABASE_DEFAULT As Name ,o.[type] COLLATE DATABASE_DEFAULT AS [Type] ,'['+SCHEMA_NAME(o.schema_id)+'].['+o.[name]+']' as FullName ,CASE WHEN o.[type] IN ('U','S') THEN t.tablecolumns ELSE OBJECT_DEFINITION(o.object_id) END AS [Source] ,@SearchPattern as[SearchPattern] FROM sys.objects AS o LEFT JOIN @tabletbl AS t ON t.[objectid] = o.[object_id] WHERE (lower(OBJECT_DEFINITION(o.object_id)) LIKE lower(@SearchPattern) OR (t.tablecolumns like lower(@SearchPattern) OR t.tablename LIKE lower(@Searchpattern))) AND o.[type] IN ( 'C',--- = Check constraint 'D',--- = Default (constraint or stand-alone) 'P',--- = SQL stored procedure 'FN',--- = SQL scalar function 'R',--- = Rule 'RF',--- = Replication filter procedure 'TR',--- = SQL trigger 'IF',--- = SQL inline table-valued function 'TF',--- = SQL table-valued function 'U', --- = User Table 'S', --- = System Table 'V') --- = View UNION ALL SELECT *,@SearchPattern FROM @jobtbl ORDER BY 3
If you find this script useful, you’re welcome to use it. I look forward to seeing what others contribute! Thank you Bert for an awesome topic!
As always, thank you for reading.