Newbie Questions (need your help) easy questions for you gurus. 1

[Sina]

Hello everyone.
I have couple of newbie questions, if you could kindly help.
here they are:
1. what is the command to get a list of the
database and tables in each database in sybase?
2. How do I get a list of all the primary keys on a table ?
3. How do I get a list of all the foriegn keys on a table ?
4. How do I get a list of all the indexes on a table ?
5. How do I get a description on a table in sybase (like field defenitions).
6. How do I find out the list of users with access to a specific table / database (get user privilages on each db)?
7. How do I get a list of all the valid table fileds TYPES in sybase?

thank you all very much.

 

[qyllr]

hello,

here are the commands and SQLs:

1. database list - sp_heldb
table list - use the following SQL:

use databaseName
go
select name
from sysobjects
where type = 'U'
go

2-4. sp_helpconstraint tableName - should give you primary/foreign keys and indexes. you can also try sp_primarykey tableName, sp_foreignkey tableName and sp_helpindex tableName.

5. field definitions use the following SQL:

use databaseName
go
select a.name, b.name
from sysobjects a, syscolumns b
where a.id = b.id
and a.type = 'U'
go

6. sp_helprotect tableName and sp_helpuser

7. check the systypes system table:

select distinct(name)
from systypes
go

check out the following URL for more info:

http://manuals.sybase.com/onlineboo...;pt=155924?target=%N_170800_START_RESTART_N%

hth,
q.

 

[Sina]

Thank you very much for your time.

 

[Sina]

Here is just a couple of questions.
Thanks so much for the info.

8. How to List all full text indexs in a table

9. how to create
primary key
forign keys
indexes
table
database

Thank you so much.

http://www.realsoftstudio.com

SQLPorter Database Migration Tool

 

[qyllr]

hello,

try this...

8. sp_helpindex {tableName}
go

9. adding primary and foreign key constraints...

alter table {tableName}
add constraint {constraintName}
PRIMARY KEY NONCLUSTERED
({columnName})
go

alter table {tableName1}
add CONSTRAINT {foreignKeyConstraintName}
FOREIGN KEY
(columnName1)
REFERENCES
tableName2(columnName2)
go

indexes:

for primary key indexes use the example above...
with the alter table command...

unique clustered --
create unique clustered index {indexName} on {tableName}
({columnName})
on 'default'
go

nonclustered --
create nonclustered index {indexName} on {tableName}
({columnName})
on 'default'
go

database --
create database {databaseName}
[on {default | databaseDevice} [= size]
[, databaseDevice [= size]]...]
[log on databaseDevice [= size]
[, databaseDevice [= size]]...]
[with override]
[for load]

table --
create table [database.[owner].]tableName
(columnName datatype
[default {constantExpression | user | null}]
{[{identity | null | not null}]
| [[constraint constraintName]
{{unique | primary key}
[clustered | nonclustered]
[with {fillfactor | max_rows_per_page}=x]
[on segmentName]
| references [[database.]owner.]ref_table
[(ref_column)]
| check (search_condition)}]}...

you may want to check the sybase website for details and explanations...

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aserefmn/@Generic__BookView

hth,
q.

 

[Sina]

Thank you for your help very much

 

[Guest_imported]

use system stored procedures and system tables.
examples:
q.1. How to get all constraints in a table.
a.1 use sp_helpconstraint.

q.2. How to get a list of all user tables in the database.
a.2. query the sysobjetcs table where type = 'U'.

I hope this helps.