Blog

SELECT spid,
 sp.[status],
 loginame [Login],
 hostname, 
 blocked BlkBy,
 sd.name DBName, 
 cmd Command,
 cpu CPUTime,
 physical_io DiskIO,
 last_batch LastBatch,
 [program_name] ProgramName 
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
where hostname='BOARDROOM'
ORDER BY spid 

Declare @Dir VARCHAR(256)
DECLARE @CMD VARCHAR(256)
SET @Dir = 'C:\myImageFolder\'
SET @CMD = 'DIR "'+@DIR+'" /A /S'

drop table #tmp 
 
 declare @media nvarchar(2048), @fn nvarchar(2048)
 DECLARE @CMD VARCHAR(256)

set @media = 'D:\Filestore\media'

SET @CMD = 'DIR "'+@media +'" /-C /S'

CREATE TABLE #tmp 
 (returnval NVARCHAR(500), rownum INT IDENTITY(1,1))

-- Populate Temp Table with the contents of the outfiles directory
 INSERT #tmp EXEC master..xp_cmdshell @cmd

alter table #tmp add filename nvarchar(255)
 alter table #tmp add size int
 alter table #tmp add DocumentId int

select convert(int,substring(returnval, 27,9)) size, substring(returnval, 37,999) name, * from #tmp
 where returnval like '__/__/____ __:__ %'
 --e.g. '17/11/2017 11:40 2645779 2017-11-17T114021.769Z001214 Band 4 Business Administrator Job Description_SM.pdf'

update #tmp set filename = substring(returnval, 37,999)
 , size = convert(int,substring(returnval, 27,9))
 where returnval like '__/__/____ __:__ %'

update #tmp set documentid=d.documentid from
 #tmp, documents d where d.documentpath like '%' + #tmp.filename


 select 
 filename, 'D:\MyStaffFile_Files\media\' + d.documentpath, s.assignmentid, size, d.documentid, createdby, createddate
 
 from #tmp inner join documents d on #tmp.documentid = d.documentid
 inner join sections s on d.sectionid=s.sectionid
 
 where size>1000000
 order by CreatedBy, CreatedDate, Size desc --documentid desc

Got a table with lots of columns? Use this SQL to identify which have values.

My table name is manTransaction and I’m only interested in those of type = 2

declare @tn nvarchar(200)
declare @whereclause nvarchar(1024)
set @tn='manTransactionDetail' --table
set @whereclause = 'TransactionID in (select TransactionID from manTransaction where TransactionTypeID=8)' --particular records
declare @col nvarchar(200)
declare @typ int
declare @sql0 nvarchar(2048)
declare @sql nvarchar(2048)

set @sql0 = 'with a(v) as (select __COLNAME__ from __TABLENAME__ where __WHERECLAUSE__ )
 , d(v) as (select v from a group by v)
 insert into #ColumnStatus(ColName, DistinctValues) select ''__COLNAME__'', COUNT(*) from d'
set @sql0 = REPLACE(@sql0, '__TABLENAME__',@tn)
set @sql0 = REPLACE(@sql0, '__WHERECLAUSE__',@whereclause)

if OBJECT_ID('tempdb..#ColumnStatus') is not null drop table #ColumnStatus
create table #ColumnStatus(ColName nvarchar(200), DistinctValues int)
declare c cursor for
select name, system_type_id from sys.columns where object_id=object_id(@tn)
open c
fetch next from c into @col,@typ
set @sql = REPLACE (@sql0, '__COLNAME__', @col)
print @sql
exec(@sql) 
while @@FETCH_STATUS=0
begin
 fetch next from c into @col,@typ
 set @sql = REPLACE (@sql0, '__COLNAME__', @col)
 exec(@sql) 
end
close c
deallocate c
select ColName + ',' from #ColumnStatus where DistinctValues > 1

--select * from #ColumnStatus

select isnumeric('20,000.000') returns 1

select convert(float, '20,000.000') raises an error Error converting data type varchar to float.

You can’t write a isfloat() function, because try-catch is a side effect.

Here’s a snippet to walk thru your data to find duff values

declare c cursor for
select chillirecipedetailid, amount_or from tblAbRecipes
open c
declare @id int, @val nvarchar(100)
fetch next from c into @id, @val
while @@FETCH_STATUS=0
begin
 begin try
 declare @f float
 set @f = convert(float,@val)
 end try
 begin catch
 print 'duff value at ' + convert(nvarchar(20),@id) + ': ' + @val
 end catch;
 fetch next from c into @id, @val
end;
close c
deallocate c

select isnumeric('20,000.000')
select convert(float, '20,000.000')

exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'xp_cmdshell', 1;
go
reconfigure;
go

drop function sp_FileExists

if object_id('sp_FileExists') is null
begin
 declare @sql nvarchar(2000)
 set @sql='
 create function dbo.sp_FileExists(@fn nvarchar(1000)) returns bit as
 begin
 declare @result int
 exec master.dbo.xp_fileexist @fn, @result output
 if @result = 1 return 1
 return 0 
 end
 '
 exec @sql
end

select DocumentId, 'd:\MyStaffFile_Files\media\' + DocumentPath 
,case when DocumentPath is null then 'no path'
 when dbo.sp_FileExists('d:\MyStaffFile_Files\media\' + DocumentPath) = 1 then 'exists'
 else 'does not exist'
 end
from Documents