SAP Sybase ASE 15.7 sp_onerow Sample Table Row



sp_onerow Sample Table Row

This non-SAP stored procedure shows an example row from a table.

Sample Output

1> sp_onerow sysusages
2> go

Showing example data from table 'sysusages':

 colname                                                column_data
 ------------------------------------------------------ --------------------------------------------------
 dbid                                                   1
 segmap                                                 7
 lstart                                                 0
 size                                                   6656
 vstart                                                 4
 location                                               1
 unreservedpgs                                          3587
 crdate                                                 Aug  5 2013  6:18PM
 vdevno                                                 0


(return status = 0)


Stored Procedure T-SQL

use sybsystemprocs
go

create proc sp_onerow @table_name varchar(100) = "sysobjects"
as
-- Shows layout of one row of a table SAP sybase ASE 15.7
-- Alastair Cook 16th Sept 2013
-- non-SAP stored procedure

set nocount on

declare @qty_cols int, @ncount int, 
@this_sql varchar(200), @column_name varchar(100)

select @ncount = 1
--select @table_name = "customers"

select colid, column_name=name into #table_tmp from syscolumns where id = object_id(@table_name)

select colname="xxxxxxxxxxxxxxxxxxxxxxxxxxx",column_data="xxxxxxxxxxxxxxxxxxxxxxxxx" into #column_and_data_tmp where 1=0

select @qty_cols = count(*) from #table_tmp

set rowcount 1

while (@ncount <= @qty_cols)
begin

select @column_name = column_name  from #table_tmp where colid = @ncount

select @this_sql="insert #column_and_data_tmp select " + char(34)
+ @column_name + char(34) + ","
+ "isnull(convert(varchar," + @column_name + "),' ')" 
+ " from " + @table_name  + "(index " + @table_name + ")"

exec(@this_sql)

select @ncount = @ncount + 1
end

set rowcount 0

print " "
print "Showing example data from table '%1!':",@table_name
print " "
select * from #column_and_data_tmp
print " "

if ( @table_name = "sysobjects")
print "usage: sp_onerow "
print " "

drop table #column_and_data_tmp
drop table #table_tmp

go

Test First on your Development System

Sites are welcome to use the above code to provide a summary view of sybase databases and modify as required.

Contact Information

If there are any changes suggested for this page please contact me.

Alastair Cook email:
cookalastair @ btinternet.com

Last update to this page - 14th Sept 2013