Sysadmin Sql Scripts

A) Sysadmin Scripts

This is a set of sysadmin scripts I've found useful in sql

Blocking Sessions.sql

if OBJECT_ID('tempdb..#currentBlocks') is not null drop table #currentBlocks

go

WITH [Blocking]
AS (
	SELECT w.[session_id]
		,s.[original_login_name]
		,s.[login_name]
		,w.[wait_duration_ms]
		,w.[wait_type]
		,r.[status]
		,r.[wait_resource]
		,w.[resource_description]
		,s.[program_name]
		,w.[blocking_session_id]
		,s.[host_name]
		,r.[command]
		,r.[percent_complete]
		,r.[cpu_time]
		,r.[total_elapsed_time]
		,r.[reads]
		,r.[writes]
		,r.[logical_reads]
		,r.[row_count]
		,q.[text]
		,q.[dbid]
		,p.[query_plan]
		,r.[plan_handle]
	FROM [sys].[dm_os_waiting_tasks] w
		INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
		INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
		CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
		CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
	WHERE 
		w.[session_id] > 50
		AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT','ASYNC_NETWORK_IO')
)

SELECT 
	b.[session_id] AS [WaitingSessionID]
	,b.[blocking_session_id] AS [BlockingSessionID]
	,b.[login_name] AS [WaitingUserSessionLogin]
	,s1.[login_name] AS [BlockingUserSessionLogin]
	,b.[original_login_name] AS [WaitingUserConnectionLogin] 
	,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
	,b.[wait_duration_ms] AS [WaitDuration]
	,b.[wait_type] AS [WaitType]
	,t.[request_mode] AS [WaitRequestMode]
	,UPPER(b.[status]) AS [WaitingProcessStatus]
	,UPPER(s1.[status]) AS [BlockingSessionStatus]
	,b.[wait_resource] AS [WaitResource]
	,t.[resource_type] AS [WaitResourceType]
	,t.[resource_database_id] AS [WaitResourceDatabaseID]
	,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
	,b.[resource_description] AS [WaitResourceDescription]
	,b.[program_name] AS [WaitingSessionProgramName]
	,s1.[program_name] AS [BlockingSessionProgramName]
	,b.[host_name] AS [WaitingHost]
	,s1.[host_name] AS [BlockingHost]
	,b.[command] AS [WaitingCommandType]
	,b.[text] AS [WaitingCommandText]
	,b.[row_count] AS [WaitingCommandRowCount]
	,b.[percent_complete] AS [WaitingCommandPercentComplete]
	,b.[cpu_time] AS [WaitingCommandCPUTime]
	,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
	,b.[reads] AS [WaitingCommandReads]
	,b.[writes] AS [WaitingCommandWrites]
	,b.[logical_reads] AS [WaitingCommandLogicalReads]
	,b.[query_plan] AS [WaitingCommandQueryPlan]
	,b.[plan_handle] AS [WaitingCommandPlanHandle]
into #currentBlocks
FROM [Blocking] b
	INNER JOIN [sys].[dm_exec_sessions] s1 ON b.[blocking_session_id] = s1.[session_id]
	INNER JOIN [sys].[dm_tran_locks] t ON t.[request_session_id] = b.[session_id]
WHERE 
	t.[request_status] = 'WAIT'

select * 
from master.sys.sysprocesses 
where SPID in (
	select BlockingSessionID --this subquery returns all spids that are lock source (based on the fact that they are blocking another spid but are not themselves waiting on anything else)
	from #currentBlocks
	where
		BlockingSessionID not in (select WaitingSessionID from #currentBlocks) --BlockingSessionID is not blocked by another command
	)

Check Procedure Info.sql

declare @CheckText varchar(max) = '%'

select * 
from sys.procedures 
where name=@CheckText

Check Procedures Executed at Specific Time.sql

declare @FromDate datetime = '2016-10-14 09:00:00'
declare @ToDate datetime = '2016-10-14 10:00:00'

select *
from (
	SELECT TOP 50
		convert(varchar,
			dateadd(MILLISECOND,qs.total_worker_time / 1000, 0) --milliseconds
			, 114
		) AS total_cpu_time_span
		,convert(varchar,
			dateadd(MILLISECOND,qs.max_worker_time / 1000, 0) --milliseconds
			, 114
		)   AS max_cpu_time_span
		,convert(varchar,
			dateadd(MILLISECOND,qs.total_elapsed_time / 1000, 0) --milliseconds
			, 114
		) total_elapsed_time_span
		,qs.last_elapsed_time / 1000 [last_elapsed_time_millisecond]
		,convert(varchar,
			dateadd(MILLISECOND,qs.last_elapsed_time / 1000, 0) --milliseconds
			, 114
		) last_elapsed_time_span
		,convert(varchar,
			dateadd(MILLISECOND,qs.max_elapsed_time / 1000, 0) --milliseconds
			, 114
		) [max_elaped_time_span]
		qs.last_rows,
		,t.text
		,qp.query_plan
		,db_name(t.dbid) + '.' + object_name(t.objectid, t.dbid) [objectname]
		,t.encrypted
		, qs.last_execution_time [start_time]
		, dateadd(millisecond, last_elapsed_time / 1000, qs.last_execution_time) [end_time]
	FROM   sys.dm_exec_query_stats qs 
       CROSS apply sys.Dm_exec_sql_text(plan_handle) AS t 
       CROSS apply sys.Dm_exec_query_plan(plan_handle) AS qp 
	ORDER  BY qs.max_worker_time DESC
) s
	where
		s.start_time<@ToDate
		and s.end_time>@FromDate
	order by start_time asc

Checksum Object Definitions.sql

--This one is kinda obvious but I like it

declare @SprocName varchar(500) = 'DashboardNotificationPoolList2'

use xxx

select checksum(object_definition(object_id(@SprocName)))

Delete From All Tables.sql

EXEC sp_MSforeachtable @command1 = 'DELETE FROM ?'

Find Jobs With Step Containing.sql

declare @CheckText varchar(max) = '%'

select distinct [name] [Job Name], [step_name] + ' (Step #'+cast([step_id] as varchar)+')' [Job Step ID], [command] [Command]
from msdb..sysjobs sj
	join msdb..sysjobsteps sjs on sj.job_id=sjs.job_id
where
	[command] like @CheckText

Find Procedure with Name.sql

declare @CheckText varchar(max) = '%'

select * 
from sys.procedures p
	inner join sys.schemas s on p.schema_id=s.schema_id
where
	p.name like @CheckText

Find Stored Procedures Containing.sql

declare @CheckText varchar(max) = '%'

SELECT name [Stored Procedure Name], Object_definition(object_id) [Definition]
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE @CheckText

Fragmentation.sql

declare @db_id varchar(max) = --''
declare @Tables table (TableName varchar(100))
--insert into @Tables values ('tablename')

select object_name(stat.[object_id]) [Object Name], idx.name [Index Name], index_type_desc, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count
from sys.dm_db_index_physical_stats(db_id(@db_id), null, null, null,null) stat
	inner join sys.indexes idx on stat.[object_id]=idx.[object_id] and stat.[index_id]=idx.[index_id]
where 
	 object_name(stat.[object_id]) in @Tables
order by
	object_name(stat.[object_id]) asc

Get Stored Procedure Change Info.sql

declare @CheckText varchar(max)='%'

select s.name+'.'+p.[name] [Stored Procedure Name], [create_date], [modify_date]
from sys.procedures p
	join sys.schemas s on p.[schema_id]=s.[schema_id]
where 
	p.name like @CheckText
	and p.[Type]='P'

Job Schedule Information.sql

USE msdb
Go

/****************************
	These reference tables have been generated based on: https://msdn.microsoft.com/en-us/library/ms178644.aspx
********************************/

declare @FrequencyType table (FrequencyTypeID int, FriendlyName varchar(50))
insert into @FrequencyType
values (1, 'One time only')
	, (4, 'Daily')
	, (8, 'Weekly')
	, (16, 'Monthly')
	, (32, 'Monthly relative')
	, (64, 'When Sql Server Agent Starts')
	, (128, 'Runs when computer is idle')

declare @FrequencySubdayType table (FrequencySubdayTypeID int, FriendlyName varchar(50))
insert into @FrequencySubdayType
values (1, 'At the specified time')
	, (2, 'Seconds')
	, (4, 'Minutes')
	, (8, 'Hours')

declare @FrequencyRelativeInterval table (FrequencyRelativeIntervalID int, FriendlyName varchar(50))
insert into @FrequencyRelativeInterval
values (0, 'Unused')
	, (1, 'First')
	, (2, 'Second')
	, (4, 'Third')
	, (8, 'Fourth')
	, (16, 'Last')


declare @JobRunDuration table (JobID uniqueidentifier, AverageRunDuration int, MaxDuration int)
insert into @JobRunDuration (JobID, AverageRunDuration, MaxDuration)
select job_id, avg(run_duration) [average_run_duration], max(run_duration) [max_run_duration]
from (
	select job_id
		, run_duration
		, row_number() over (partition by job_id order by sjh.run_date desc, sjh.run_time desc) [RunIndex]
	from dbo.sysjobhistory sjh
	where 
		run_status=1 --only successful execution
		and step_id=0
) s
where 
	[RunIndex]<=10
group by 
	job_id
	

SELECT 
	sj.[Name], 
	sj.[Enabled],
	ss.freq_interval,
	ft.FriendlyName [FrequencyType], 
	active_start_date,
	active_start_time,
	jd.AverageRunDuration,
	jd.MaxDuration,
    ss.freq_subday_interval, --0 means once
	fst.FriendlyName [FrequencySubdayType],
	ss.freq_recurrence_factor,
	fri.FriendlyName [FrequencyRelativeIntervalType]
FROM dbo.sysjobs sj
	left outer join dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
	inner join dbo.sysschedules ss ON sjs.schedule_id = ss.schedule_id 
	join @FrequencyType ft on ss.freq_type=ft.FrequencyTypeID
	join @FrequencySubdayType fst on ss.freq_subday_type=fst.FrequencySubdayTypeID
	join @FrequencyRelativeInterval fri on ss.freq_relative_interval=fri.FrequencyRelativeIntervalID
	join @JobRunDuration jd on sj.job_id=jd.JobID
ORDER BY active_start_date,active_start_time

Recent Query Executions by Elapsed time.sql

declare @DBName varchar(10)=''

select * 
from (
	SELECT top 50 
		deqs.last_execution_time AS [Time], 
		dest.text AS [Query], 
		object_name(dest.objectid,db_id(@DBName)) [object name], 
		deqs.total_worker_time, 
		deqs.total_elapsed_time /*microseconds*/ / 1000.00 /*milliseconds*/ / 1000.00 /*seconds*/ [total_elapsed_time(ss)]
	FROM sys.dm_exec_query_stats AS deqs
	CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
	WHERE dest.dbid = DB_ID(@DBName) 
	ORDER BY deqs.last_execution_time DESC
) recent_executions 
order by [total_elapsed_time(ss)] desc

Restore Jobs - A) Perform Restore

set xact_abort on

begin transaction [import]
	
	create table #permittedJobs (
		job_id uniqueidentifier
	)

	insert into #permittedJobs
	select job_id
	from MSDB_BACKUP..sysjobs

	/*************************************************************************
	***	Job Import
	**************************************************************************/
	
	select 'Notification: Job does not exist in backup' [Notification], * from msdb..sysjobs where job_id not in (select job_id from #permittedJobs)

	create table #jobRefreshSummary (
		[action] varchar(20)
		, inserted_job_id uniqueidentifier
		, inserted_job_name varchar(max)
		, deleted_job_id uniqueidentifier
		, deleted_job_name varchar(max)
	)
	
	merge into msdb..sysjobs [t]
	using (
		select s.*
		from MSDB_BACKUP..sysjobs s
			inner join #permittedJobs p on s.job_id=p.job_id
		) as [s]
	on t.job_id=s.job_id
	when matched then --Update the existing jobs if the job_id is found
		update set
			t.originating_server_id=s.originating_server_id, t.name=s.name, t.[enabled]=s.[enabled], t.[description]=s.[description], t.start_step_id=s.start_step_id, t.category_id=s.category_id, t.owner_sid=0x01, --sa 
			t.notify_level_eventlog=s.notify_level_eventlog, t.notify_level_email=s.notify_level_email, t.notify_level_netsend=s.notify_level_netsend, t.notify_level_page=s.notify_level_page, 
			t.notify_email_operator_id=s.notify_email_operator_id, t.notify_netsend_operator_id=s.notify_netsend_operator_id, t.notify_page_operator_id=s.notify_page_operator_id, t.delete_level=s.delete_level, 
			t.date_created=s.date_created, t.date_modified=s.date_modified, t.version_number=s.version_number
	--when not matched by source then --Do nothing, because the job was created for this instance only (Ex: Ref2 Database restore)
	when not matched then --If the job is missing from this environment, then import it
		insert ([job_id], originating_server_id, name, [enabled], [description], start_step_id, category_id, [owner_sid], --sa 
			notify_level_eventlog, notify_level_email, notify_level_netsend, notify_level_page, notify_email_operator_id, notify_netsend_operator_id, notify_page_operator_id, delete_level, 
			date_created, date_modified, version_number)
		values (s.[job_id], s.originating_server_id, s.name, s.[enabled], s.[description], s.start_step_id, s.category_id, 0x01, --sa 
			s.notify_level_eventlog, s.notify_level_email, s.notify_level_netsend, s.notify_level_page, s.notify_email_operator_id, s.notify_netsend_operator_id, s.notify_page_operator_id, s.delete_level, 
			s.date_created, s.date_modified, s.version_number)
	output $action, inserted.job_id, inserted.name, deleted.job_id, deleted.name into #jobRefreshSummary
	;
	
	select * from #jobRefreshSummary
	
	/*************************************************************************
	***	Job Step Import
	**************************************************************************/

	create table #stepRefreshSummary (
		[action] varchar(20)
		, inserted_job_id uniqueidentifier
		, inserted_step_name varchar(max)
		, deleted_job_id uniqueidentifier
		, deleted_step_name varchar(max)
	)

	merge into msdb..sysjobsteps [t]
	using (
		select s.*
		from MSDB_BACKUP..sysjobsteps s
			inner join #permittedJobs p on s.job_id=p.job_id
		) as [s]
	on t.job_id=s.job_id and t.step_uid=s.step_uid
	when matched then
		update
		set t.step_id=s.step_id, t.step_name=s.step_name, t.subsystem=s.step_name, t.command=s.command, t.flags=s.flags, t.additional_parameters=s.additional_parameters, t.cmdexec_success_code=s.cmdexec_success_code, 
		t.on_success_action=s.on_success_action, t.on_success_step_id=s.on_success_step_id, t.on_fail_action=s.on_fail_action, t.on_fail_step_id=s.on_fail_step_id,
		t.[server]=s.[server], t.database_name=s.database_name, t.database_user_name=s.database_user_name, 
		t.retry_attempts=s.retry_attempts, t.retry_interval=s.retry_interval, t.os_run_priority=s.os_run_priority, t.output_file_name=s.output_file_name,
		t.last_run_outcome=s.last_run_outcome, t.last_run_duration=s.last_run_duration, t.last_run_retries=s.last_run_retries, t.last_run_date=s.last_run_date, t.last_run_time=s.last_run_time, t.proxy_id=s.proxy_id
	when not matched by source and t.job_id in (select job_id from MSDB_BACKUP..sysjobs) then
		delete
	when not matched then
		insert (job_id, step_id, step_name, subsystem, command, flags, additional_parameters, cmdexec_success_code, 
			on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, 
			[server], database_name, database_user_name, 
			retry_attempts, retry_interval, os_run_priority, output_file_name, 
			last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id, step_uid)
		values (s.job_id, s.step_id, s.step_name, s.subsystem, s.command, s.flags, s.additional_parameters, s.cmdexec_success_code, 
			s.on_success_action, s.on_success_step_id, s.on_fail_action, s.on_fail_step_id, 
			s.[server], s.database_name, s.database_user_name, 
			s.retry_attempts, s.retry_interval, s.os_run_priority, s.output_file_name, 
			s.last_run_outcome, s.last_run_duration, s.last_run_retries, s.last_run_date, s.last_run_time, s.proxy_id, s.step_uid)
	output $action, inserted.job_id, inserted.step_name, deleted.job_id, deleted.step_name into #stepRefreshSummary
	;

	select * from #stepRefreshSummary order by [action]
	
	/*************************************************************************
	***	Job Schedule Import
	**************************************************************************/

	create table #scheduleRefreshSummary (
		[action] varchar(20)
		, inserted_schedule_id uniqueidentifier
		, inserted_schedule_name varchar(max)
		, deleted_schedule_id uniqueidentifier
		, deleted_schedule_name varchar(max)
	)
	
	declare @scheduleIdIncrement int = (select max(schedule_id) from msdb..sysschedules) --this schedule increment is for newly imported schedules

	create table #scheduleMap (
		SourceScheduleId int,
		TargetScheduleID int
	)

	insert into #scheduleMap (SourceScheduleId, TargetScheduleId)
	select schedule_id, schedule_id + @scheduleIdIncrement
	from MSDB_BACKUP..sysschedules s
	where
		s.schedule_uid not in (select t.schedule_uid from msdb..sysschedules t)

	insert into #scheduleMap (SourceScheduleId, TargetScheduleID)
	select  s.schedule_id, t.schedule_id
	from msdb..sysschedules t
		inner join MSDB_BACKUP..sysschedules s on t.schedule_uid=s.schedule_uid


	set identity_insert msdb..sysschedules on
	
	merge into msdb..sysschedules [t]
	using (
		select isnull(m.TargetScheduleId, s.schedule_id) [schedule_id], --use the mapped target id if exists
			schedule_uid, originating_server_id, name, owner_sid, [enabled], 
			freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, 
			active_start_date, active_end_date, active_start_time, active_end_time, 
			date_created, date_modified, version_number
		from MSDB_BACKUP..sysschedules s
			inner join MSDB_BACKUP..sysjobschedules js on s.schedule_id=js.schedule_id
			inner join #permittedJobs p on js.job_id=p.job_id
			left outer join #scheduleMap m on s.schedule_id=m.SourceScheduleId
		) as [s]
	on t.schedule_uid=s.schedule_uid
	when matched then
		update set
			t.originating_server_id=s.originating_server_id, t.name=s.name, t.owner_sid=s.owner_sid, t.[enabled]=s.[enabled], 
			t.freq_type=s.freq_type, t.freq_interval=s.freq_interval, t.freq_subday_type=s.freq_subday_type, t.freq_subday_interval=s.freq_subday_interval, t.freq_relative_interval=s.freq_relative_interval, t.freq_recurrence_factor=s.freq_recurrence_factor, 
			t.active_start_date=s.active_start_date, t.active_end_date=s.active_end_date, t.active_start_time=s.active_start_time, t.active_end_time=s.active_end_time, 
			t.date_created=s.date_created, t.date_modified=s.date_modified, t.version_number=s.version_number
	--when not matched by source and t.schedule_uid in (select schedule_uid from MSDB_BACKUP..sysschedules) then
	--	delete
	when not matched then
		insert (schedule_id, schedule_uid, originating_server_id, name, owner_sid, [enabled], 
			freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, 
			active_start_date, active_end_date, active_start_time, active_end_time, 
			date_created, date_modified, version_number)
		values (s.schedule_id, s.schedule_uid, s.originating_server_id, s.name, s.owner_sid, s.[enabled], 
			s.freq_type, s.freq_interval, s.freq_subday_type, s.freq_subday_interval, s.freq_relative_interval, s.freq_recurrence_factor, 
			s.active_start_date, s.active_end_date, s.active_start_time, s.active_end_time, 
			s.date_created, s.date_modified, s.version_number)
	output $action, inserted.schedule_uid, inserted.name, deleted.schedule_uid, deleted.name into #scheduleRefreshSummary
	;
	
	set identity_insert msdb..sysschedules off

	select * from #scheduleRefreshSummary order by [action]
	
	
	/*************************************************************************
	***	Job Schedule Import
	**************************************************************************/

	create table #jobScheduleRefreshSummary (
		[action] varchar(20)
		, inserted_job_id uniqueidentifier
		, inserted_schedule_id int
		, deleted_job_id uniqueidentifier
		, deleted_schedule_id int
	)
		
	merge into msdb..sysjobschedules [t]
	using (
	select isnull(m.TargetScheduleId, schedule_id) [schedule_id], s.job_id, next_run_date, next_run_time
		from MSDB_BACKUP..sysjobschedules s
			inner join #permittedJobs p on s.job_id=p.job_id
			left outer join #scheduleMap m on s.schedule_id=m.SourceScheduleId
		) as [s]
	on t.job_id=s.job_id
	when matched then
		update set
			t.schedule_id=s.schedule_id, t.next_run_date=s.next_run_date, t.next_run_time=s.next_run_time
	--when not matched by source and t.schedule_uid in (select schedule_uid from MSDB_BACKUP..sysschedules) then
	--	delete
	when not matched then
		insert (schedule_id, job_id, next_run_date, next_run_time)
		values (s.schedule_id, s.job_id, s.next_run_date, s.next_run_time)
	output $action, inserted.job_id, inserted.schedule_id, deleted.job_id, deleted.schedule_id into #jobScheduleRefreshSummary
	;

	select * from #jobScheduleRefreshSummary

rollback transaction [import]

Restore Jobs - B) Disable Restored Jobs

--This is a temporary step while we investigate the full requirements

update msdb..sysschedules
set [enabled]=0
where schedule_uid in (
	select schedule_uid 
	from MSDB_BACKUP..sysschedules
)

Search all Columns of All Tables.sql

--This is extremely slow!!! Do not run in production during business hours.

declare @CheckText varchar(max) = '%'
 
 
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME(@CheckText,'''')
 
WHILE @TableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
         
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
         
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END
 
SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results