Hi there,
Does anyone know how I can query the database to find what devices a database uses?
There is the sysdatabases table and the sysdevices table but I don't know how to link them
Anyone know?
A:
The best way is to run sp_helpdb against the database you're interested in:
1> sp_helpdb tempdb2
2> go
... other stuff here...
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------------- ----------------
tempdb2data 2048.0 MB data only Dec 17 2008 11:42AM 2086568
tempdb2log 2048.0 MB log only Dec 17 2008 11:42AM not applicable
tempdb2log 2048.0 MB log only Dec 17 2008 11:42AM not applicable
tempdb2data 2048.0 MB data only Dec 17 2008 11:43AM 2088960
tempdb2log 4096.0 MB log only Dec 17 2008 11:44AM not applicable
--------------------------------------------------------------
log only free kbytes = 8355836
AdamH
2010-10-22 10:04:38
Thanks Adam. When I run the same command I get the following columns returned: name,db_size,owner,dbid,created,durability,status ... there is no device information! Our outputs are different. Do you know why? I'm using Sybase version 15.5
Bob
2010-10-22 13:16:57
Docs for sp_helpdb are here for 15.5 http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36273.1550/html/sprocs/X90458.htm and they suggest the same syntax.
AdamH
2010-10-22 13:31:05
I think the issue will be that sp_helpdb returns rows using a select and also prints out stuff. If you run it from the command line with isql it will display correctly. If you run it from the 15.5 version of Sql Advantage(not sure if name stayed, I've only used 12.x), then some data will be displayed in the grid and some on the "messages" output.
AdamH
2010-10-22 13:32:45
I'm an idiot! When I select "Results set 2" I can see what i need! Thanks a million Adam, really appreciate it
Bob
2010-10-22 13:35:01
Hello Bob Can u please post what Sysbase SQL Query you are using to find what devices a database uses..Becase sp_helpdb TEST query return only following columns name,db_size,owner,dbid,created,durability,status
John
2010-10-30 06:59:32
A:
1 Just a note re your first question. If you USE database first, you will get even more detail in the report.
2 Do you still need the second question answered, how to link sysdatabase and sysdevices, as in, are you writing queries against the catalogue ? If so, I need your ASE version, the answers are different.
PerformanceDBA
2010-10-23 11:15:57