Script to Return a Transposed Data Row

,

I often do the following when administering databases "select * from Table where PrimaryKey=value" and find the output in Query Analyzer is inconvenient to view when the table has many wide columns (which is often the case). This script returns a single entry from a table but "transposed" so that the data flows down the page not accross it. I know this sounds trivial, but it's convenient, so I thought I'd share it.

(Note: T-SQL doesn't support arrays, as far as I'm aware, so the code is a little ugly.)

create procedure dbo.GetTableEntry
    @Table as sysname,
    @Criterion as nvarchar(4000)
as
    declare @column sysname
    declare @sql0 nvarchar(4000) set @sql0=N''
    declare @sql1 nvarchar(4000) set @sql1=N''
    declare @sql2 nvarchar(4000) set @sql2=N''
    declare @sql3 nvarchar(4000) set @sql3=N''
    declare @sql4 nvarchar(4000) set @sql4=N''
    declare @sql5 nvarchar(4000) set @sql5=N''
    declare @sql6 nvarchar(4000) set @sql6=N''
    declare @sql7 nvarchar(4000) set @sql7=N''
    declare @sql8 nvarchar(4000) set @sql8=N''
    declare @sql9 nvarchar(4000) set @sql9=N''

    declare
        columncurs
    cursor
        local read_only fast_forward 
    for
        select
            C.name
        from
            syscolumns as C
        join
            sysobjects as O on O.id=C.id
        where
            O.xtype in ('U','S')
        and O.name=@table
        order by
            C.colid asc

    open columncurs

    fetch next from
        columncurs
    into
        @column

    while @@fetch_status=0
    begin
        if @sql0=N''
            set @sql0=N'select top 1 '''+@column+''' as [Column],convert(varchar,'+@column+N') as [Value] from '+@table+N' where '+@criterion
        else
            set @sql0=N' union all select top 1 '''+@column+''' as [Column],convert(varchar,'+@column+N') as [Value] from '+@table+N' where '+@criterion

        if len(@sql0)+len(@sql1)<=4000
            set @sql1=@sql1+@sql0
        else if len(@sql0)+len(@sql2)<=4000
            set @sql2=@sql2+@sql0
        else if len(@sql0)+len(@sql3)<=4000
            set @sql3=@sql3+@sql0
        else if len(@sql0)+len(@sql4)<=4000
            set @sql4=@sql4+@sql0
        else if len(@sql0)+len(@sql5)<=4000
            set @sql5=@sql5+@sql0
        else if len(@sql0)+len(@sql6)<=4000
            set @sql6=@sql6+@sql0
        else if len(@sql0)+len(@sql7)<=4000
            set @sql7=@sql7+@sql0
        else if len(@sql0)+len(@sql8)<=4000
            set @sql8=@sql8+@sql0
        else if len(@sql0)+len(@sql9)<=4000
            set @sql9=@sql9+@sql0
        else
        begin
            raiserror('Buffer overflow while preparing batch.',1,1)
            return
        end

        fetch next from
            columncurs
        into
            @column
    end

    close columncurs

    execute(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9)

    if @@error<>0
    begin
        raiserror('Error in prepared batch.',1,1)
        print @sql1
        print @sql2
        print @sql3
        print @sql4
        print @sql5
        print @sql6
        print @sql7
        print @sql8
        print @sql9
    end
go
-- this is an example usage
exec GetTableEntry 'Securities','SecurityCode=363'

Rate

Share

Share

Rate