T-SQL Tuesday #104: Code I’d Hate To Live Without

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.