public
Last active

spShog9

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
create proc spShog9
as
set nocount on
create table #results (row varchar(max), id int)
declare @currentCol varchar(100)
declare @AvgLength int
declare @MaxLength int
declare @colNames varchar(max)
set @colNames = '<h3>'
select identity(int,1,1) as row__id, * into #tt from #t
select name, AvgLength = 0, MaxLength = 0
into #cols
from tempdb.dbo.syscolumns where id = OBJECT_ID('tempdb.dbo.#t')
order by colorder
-- lazy sam
select * into #copy from #cols
while exists (select top 1 1 from #copy)
begin
select top 1 @currentCol = name from #copy
delete from #copy where name = @currentCol
exec ('update #cols
set
AvgLength = (select AVG(len(cast([' + @currentCol + '] as varchar(1000)))) from #t),
MaxLength = (select 1 + MAX(len(cast([' + @currentCol + '] as varchar(1000)))) from #t) where name = ''' + @currentCol + '''
')
end
drop table #copy
insert #results
select '', row__id from #tt
if exists (select top 1 1 from #cols where name = 'PostId')
begin
if exists (select top 1 1 from #cols where name = 'PostText')
begin
select @MaxLength = MaxLength from #cols where name = 'PostText'
if @MaxLength > 40
set @MaxLength = 40
update r set row = '<a href="http://stackoverflow.com/questions/' + cast(PostId as varchar(max)) + '">' +
case when len(PostText) = 0 then 'unknown' else left(PostText, 40) end +
'</a> ' + REPLICATE(' ', @MaxLength - LEN(case when len(PostText) > 40 then '----- ----- ----- ----- ----- ----- ----' else PostText end))
from #results r
join #tt t on r.id = t.row__id
set @colNames = @colNames + 'Post' + REPLICATE('&nbsp;', @MaxLength - 4)
delete from #cols where name in ('PostId','PostText')
end
end
 
if exists (select top 1 1 from #cols where name = 'UserId')
begin
if exists (select top 1 1 from #cols where name = 'UserName')
begin
select @MaxLength = MaxLength from #cols where name = 'UserName'
update r set row = '<a href="http://stackoverflow.com/users/' + cast(UserId as varchar(max)) + '">' +
case when len(UserName) = 0 then 'unknown' else UserName end +
'</a>' + REPLICATE(' ', @MaxLength - LEN(case when len(UserName) = 0 then 'unknown' else UserName end))
from #results r
join #tt t on r.id = t.row__id
set @colNames = @colNames + 'User' + REPLICATE('&nbsp;', @MaxLength - 4)
delete from #cols where name in ('UserName','UserId')
end
end
declare @sql nvarchar(max)
while exists (select top 1 1 from #cols)
begin
select top 1 @currentCol = name, @MaxLength = MaxLength from #cols
delete from #cols where name = @currentCol
if (@MaxLength < LEN(@currentCol))
SET @MaxLength = Len(@currentCol) + 1
set @sql =
N'update r
set row = row + CAST([' + @currentCol +'] as varchar(max)) +
REPLICATE('' '', ' + cast(@MaxLength as varchar(max)) + ' - LEN(CAST([' + @currentCol + '] as varchar(max))))
from #results r
join #tt t on r.id = t.row__id'
exec sp_executesql @sql, N''
set @colNames = @colNames + @currentCol + isnull(REPLICATE('&nbsp;', @MaxLength - LEN(@currentCol)), ' ')
end
drop table #cols
drop table #tt
set nocount off
select row = @colNames + '</h3>'
union all
select '<pre>'
union all
select row from #results
union all
select '</pre>'
set nocount on
drop table #results

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.