'dbname' and 'devname' for 'sp_addsegment' ?

To use 'sp_addsegment' I need  'dbname'
and 'devname'.  How  to know these params?
I run ' sp_databases'  and  I get 'master', 'model'
'pubs2', 'sybsystemprocs', 'tempdb'.  I have also
some tables 'Customer',  'Supplier' and others.
What 'dbname' corresponds to my tables?
How I get it?  And how I get 'devname'.
Thanks in advance.
Who is Participating?
dk99Connect With a Mentor Commented:

From the output of sp_databases, It seems that you do not have any user databases except system databases like master,model,sybsystemprocs,tempdb2 and pubs2. Have you created any table by name like Customer and Supplier ? If you have created in any of the databases and want to know your tables belong to which databases, you can use the command like :
use database_name
select * from sysobjects where name = "table_name"

If it returns any row, the you confirm your table belongs to the database.

To get the device names from server
use the command sp_helpdevice

To know which devices are used in the database use the command like :

sp_helpdb database_name.

Hope this will be easier to find out instead of writing complex query.

Let me know if you need more clarification.


You need to check for all databases. In your cases, you might need to run 5 times maximum.
In your case, since you have only 5 databases , it is use to manually use the above command to ascertain the database name instead of writing a complex query to join all system tables for that.
1)You have to first make a device using disk init command
2)then u get a device which the syabse stores has in sysdevices
this device only can be used to store database.
Let me explain in detail that if you need to now which database ur working.if its Isql then run
>select db_name()
this will give the name of the databae ur working on.
Since u have told that after running sp_databases you get only the default databases u get.That means u have not created the database.
the above said when u get the tables when u login run the
select db_name().
This will give the database ur in.
1)Run select * from sysdevices from master database.
u have to go the master database by giving the use master command.
>select * from master..sysdevices
will give a devname
Please before starting anything meet ur system administrator or please do read the database installation maual before doing anything

Any way I will give u a set by step procdure of making a
make a devname with disk init
next run sp_addsegement
then creat table and have segment_name defined for placing that table on that particular segement.

This is just an addition to my previous answer :

sp_helpdevice shows you what are the devices available in server.
If you want to know what are the devices used for which databases , the following query may help you :

select  b.name,d.name ,convert(varchar(5), segmap),
  convert(char(5), size / 512)
from sysusages u, sysdevices d, sysdatabases b
where vstart between low and high
and u.dbid = b.dbid
order by u.dbid, lstart

However from your question, I understand you want to know the name of the database where you have created your tables( without knowing the database) and also want to know how to find the device names available in the server.

If you want to create a new device , new database and segments, then you may need to follow the steps mentioned by Ajith_99.
klivlendAuthor Commented:
 Thank you!
  I used all these advices,
  my suggestion 100 points
  to dk99 and 100 to ajith_29.
  user klivlend.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.