How to rename database in Sybase

Stored procedure sp_renamedb is used to change the user database.

sp_renamedb dbname, new_name

Where
dbname is existing database name.
new_name is the new database name.

Before you rename the database, do the following things.

1. Take full backup of database.
2. Database must be set in single user mode using sp_dboption procedure. After rename, we have to set it back to multi-user mode.
3. sp_renamedb fails if any table in the database references, or is referenced by, a table in another database. Use the following query to determine which tables and external databases have foreign key constraints on primary key tables in the current database:

For foreign key references

select object_name(tableid), db_name(forgndbid) from sysreferences where forgndbid is not null

For primary key references

select object_name(reftabid), db_name(prmrydbid) from sysreferences where prmrydbid is not null

If there are any databases references, drop them using alter table command.

Rename database in Sybase:

1. Database names information is available in sysdatabases table of master database.

1> select name from sysdatabases 2> go name ------------------------------------------------------------ fxdb master model sybsystemdb sybsystemprocs tempdb vijay

2. Set database in single -user mode.

1> sp_dboption vijay, 'single user','true' 2> go Database option 'single user' turned ON for database 'vijay'. Running CHECKPOINT on database 'vijay' for option 'single user' to take effect. (return status = 0)

To check single user mode is on or not.

1> sp_helpdb vijay 2> go name db_size owner dbid created durability lobcomplvl inrowlen status ---------- -------------------------- ---------- -------- ------------------------ -------------------- -------------------- ---------------- -------------------------------------------------------------- vijay 10200.0 MB sa 5 Sep 14, 2017 full 0 NULL single user, mixed log and data

3. Now we are going to change database ‘vijay’ to ‘new name comp_records’.
Note: Check any references.

1> sp_renamedb vijay, prod1 2> go Database is renamed and in single-user mode. System Administrator (SA) must reset it to multi-user mode with sp_dboption.

4. Put database in multi-user mode.

1> sp_dboption prod1,'single user','false' 2> go Database option 'single user' turned OFF for database 'prod1'. Running CHECKPOINT on database 'prod1' for option 'single user' to take effect.

5. Verify the name in sysdatabases.

1> select name from sysdatabases 2> go name ------------------------------------------------------------ fxdb master model prod1 sybsystemdb sybsystemprocs tempdb (7 rows affected)

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: