IT Errors and Solutions Blog

Tested on SQL Server 2000 and 2005

SELECT sj.job_id,
    job_name = sj.name,
    status = CASE sjh.run_status WHEN 0 THEN ‘Failed’ WHEN 1 THEN ‘Succeeded’ WHEN 3 THEN ‘Canceled’ WHEN 4 THEN ‘In-progress message’ END,
    sjh.message,
    sjh.run_date,
    sjh.run_time,
    sjh.run_duration,
    CASE sjh.run_date WHEN 0 THEN NULL ELSE
    convert(datetime,
            stuff(stuff(cast(sjh.run_date as nchar(8)), 7, 0, ‘-’), 5, 0, ‘-’) + N’ ‘ +
            stuff(stuff(substring(cast(1000000 + sjh.run_time as nchar(7)), 2, 6), 5, 0, ‘:’), 3, 0, ‘:’),
            120) END AS [StartTime],
     DATEADD(second,sjh.run_duration,
     CASE sjh.run_date WHEN 0 THEN NULL ELSE
     convert(datetime,
             stuff(stuff(cast(sjh.run_date as nchar(8)), 7, 0, ‘-’), 5, 0, ‘-’) + N’ ‘ +
             stuff(stuff(substring(cast(1000000 + sjh.run_time as nchar(7)), 2, 6), 5, 0, ‘:’), 3, 0, ‘:’),
             120) END)AS [EndTime],
    sjh.server FROM msdb.dbo.sysjobhistory sjh
    LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON
(sjh.operator_id_emailed = so1.id)
    LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON
(sjh.operator_id_netsent = so2.id)
    LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON
(sjh.operator_id_paged = so3.id),
    msdb.dbo.sysjobs_view sj
WHERE sjh.step_id = 0

SELECT  SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

declare @cmd sysname
 select @cmd=’del “C:\Program Files\Microsoft SQL Server\Mssql\BACKUPNorthwind.bak”‘
exec xp_cmdshell @cmd,no_output

BACKUP DATABASE Northwind

    TO DISK = ‘C:\Program Files\Microsoft SQL Server\Mssql\BACKUP\Northwind.bak’

set nocount on
DECLARE Validate_Cursor CURSOR FOR
–SELECT

DECLARE @var (DataType)

FETCH NEXT FROM Validate_Cursor into @var
WHILE @@FETCH_STATUS = 0

BEGIN
 ———————————————
 –Do Something SELECT, UPDATE, DELETE
 ———————————————

FETCH NEXT FROM Validate_Cursor into @var
END

CLOSE Validate_Cursor

DEALLOCATE Validate_Cursor

Tested on SQL Server 7.0, 2000 and 2005 

You have to supply value to search and for what data type…

Script would return all tables with all columns for matching criteria….

examples to execute:

 rf_helpfindvalueall ‘Value_to_Search’,'VARCHAR’  — replace Value_to_Search with actual value

rf_helpfindvalueall ’4′, ‘INT’

********************************************

/*
SUPER POWER SEARCH
Data Types accepted:
varchar, int, bit, datetime, nchar,text,ntext,money

Created by Roman Feldblum (MCDBA, MCSD, MCSA, MCP, CCA)

*/
create proc rf_helpfindvalueall
@value varchar(50),@columndatatype varchar(50)
WITH ENCRYPTION
as

declare @tablename varchar(100)
declare @towner varchar(100)
declare @columnname varchar(100)
declare @columntype varchar(100)
declare @columnvalue varchar(2000)
declare @sql varchar(1000)
declare @i int
create table #tt(TableName varchar(100),ColumnName varchar(100),Active int,ColumnValue varchar(2000))
set nocount on
DECLARE Validate_Cursor CURSOR FOR
SELECT su.name,o.name, c.name,t.name  –,o.type
from sysobjects o inner join  syscolumns c on o.id=c.id
inner join systypes t on t.xtype=c.xtype

inner join sysusers su on
su.uid=o.uid
where o.type=’U'– o.name like’t_%’
and
t.name=@columndatatype
order by o.name desc
if @columndatatype=’ntext’
begin
 select @columndatatype=’text’
end

OPEN Validate_Cursor

FETCH NEXT FROM Validate_Cursor into @towner,@tablename,@columnname,@columntype
WHILE @@FETCH_STATUS = 0

BEGIN
 ———————————————
set nocount on
set @sql=N’insert #tt select ‘+ char(39)+@towner+’.’ + @tablename + char(39) +N’ as TableName,’+
char(39) + @columnname+ char(39)

+ case @columndatatype
 when ‘text’ then
  N’ as ColumnName, count(convert(varchar(8000),[' + @columnname
 else
  N' as ColumnName, count([' + @columnname
 end

+
case @columndatatype
 when 'bit' then
  N']) as Active,convert(int,['+ @columnname + N']) as ColumnValue from ‘
 when ‘text’ then
  N’])) as Active,convert(varchar(8000),['+ @columnname + N']) as ColumnValue from ‘
 else
  N’]) as Active,convert(varchar(2000),['+ @columnname + N']) as ColumnValue from ‘
 end
 + @towner + ‘.’ + quotename(@tablename) +
case @columndatatype
 when ‘bit’ then
  N’ where convert(int,[' + @columnname + N']) like ‘
 when ‘text’ then
  N’ where convert(varchar(8000),[' + @columnname + N']) like ‘
 else
  N’ where convert(varchar(1000),[' + @columnname + N']) like ‘
 end

+ char(39)+N’%'+convert(varchar(1000),@value)+N’%'+char(39)
+
case @columndatatype
 when ‘bit’ then
  N’ GROUP BY convert(int,’
  + quotename(isnull(@columnname,0)) +’)’
 when ‘text’ then
  N’ GROUP BY convert(varchar(8000),’
  + quotename(isnull(@columnname,0)) +’)’
 else 
  N’ GROUP BY ‘
  + quotename(@columnname)
 end
–select @sql
exec (@sql)
 
if @@error<>0
begin
print @sql
end

FETCH NEXT FROM Validate_Cursor into @towner,@tablename,@columnname,@columntype
 ——————————————–
END

CLOSE Validate_Cursor

DEALLOCATE Validate_Cursor
print(‘========================================================================================================’)

SELECT count(distinct o.name) TotalScanedTables, count(c.name)TotalScanedColumns from sysobjects o inner join  syscolumns c on o.id=c.id
inner join systypes t on t.xtype=c.xtype where o.type=’U’ and t.name=@columndatatype
print(‘========================================================================================================’)

select * from #tt
print(‘========================================================================================================’)

select Distinct
TableName,ColumnName,Active,ColumnValue
from #tt where ColumnValue=@value

drop table #tt

SQL Server 7.0 and 2000 

Create new Job in SQL Server and paste code below in Step1 , schedule job to run every minut. In case of deadlock, it would notify of event.

declare @exist int
select @exist =(select count(blocked)
from sysprocesses where blocked!=0)

if @exist > 0
begin

 EXEC xp_sendmail @recipients = ‘email@email.com’,  — replace to valid email address

    @query = ‘sp_who’,
    @subject = ‘SQL Server DeadLock Watch’, @width =600
end

Tested on SQL Server 7.0, 2000 and 2005 

I was approach by my client who asked me… “..how can I find all Views and Stored Procedures that has number 113 or 114…”

Solution:

select o.name ,c.text
from sysobjects o inner join syscomments c
on o.id=c.id
where c.text like ‘%text to search%’

Just replace ‘text to search’ with a text you are searching for.