Monday 30 June 2014

TSQL : Finding the last occurrence of a character

DECLARE @fullpath VARCHAR(500)
SET @fullpath = 'E:\Masters\MP3 DJ Masters\MP3 CD 320\Artist Albums\Sonique\Hear My Cry\01 Sonique - It Feels So Good.mp3'
 
SELECT   CHARINDEX('\', REVERSE(@fullpath)) AS [CharPosition] -- Final '\' character in the data
                ,LEFT(@fullpath,LEN(@fullpath) - CHARINDEX('\', REVERSE(@fullpath))) AS [Folder]
                ,RTRIM(RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) - 1)) AS [FileName] 

Thursday 26 June 2014

SQL : Numeric Data Search Script - Version 1

After investigating another unknown database, I have adapted my Data Search Script to search for values in numeric columns.
/*
NUMERIC DATA SEARCH SCRIPT

version 1 :  

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata int
-- set string to search for here
set @searchdata = 200908

select @columncount = count(*) from information_schema.columns
 where data_type in ('int','bigint')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] = ' + CAST(@searchdata as VARCHAR(15)) + ' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select ''''' + table_name + ''''' as [table_name], [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] = ' + CAST(@searchdata  as VARCHAR(15)) + ''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('int','bigint')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10)