Month: November 2017

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