SAP Sybase ASE 15.7 sp_tablelist User Table List



sp_tablelist User Table List

This non-SAP stored procedure shows a list of user tables for a database.

Sample Output

1> sp_tablelist
2> go

user table list for database 'pushpullsigns':

   access_log_in  allowed_last_name  altest  altest_len  blurbs

   codes  customer  customers  customers2

   customers3  customers_temp  department  employee

   sales  sales_item  sales_item

(return status = 0)


Stored Procedure T-SQL

use sybsystemprocs
go

drop proc sp_tablelist
go

create proc sp_tablelist
as
-- Shows a listing of user tables for a database SAP sybase ASE 15.7
-- Alastair Cook 16th Sept 2013
-- non-SAP stored procedure

set nocount on
select name into #table_list from sysobjects where type = "U" order by name

declare table_list_cursor cursor for
select name from #table_list

declare @table_list varchar(1000), @table_list_tmp varchar(100),
@qty_tables int, @ncount int, @nacross int,@db_name varchar(30)

select @db_name = db_name()
select @nacross = 0
select @qty_tables = count(*) from #table_list
select @table_list = " "


print " "
print "user table list for database '%1!':",@db_name

open table_list_cursor
print " "
--select * from #table_list

while (@@sqlstatus = 0 )
begin

fetch table_list_cursor into @table_list_tmp

select @table_list =  @table_list + "  " + @table_list_tmp

-- print 4 across
if (@nacross > 3)
begin
print @table_list
select @table_list = " "
select @nacross = 0
print " "
end

select @nacross = @nacross + 1
end


print @table_list

print " "
close table_list_cursor
deallocate cursor table_list_cursor

drop table #table_list
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