Automatically rename Foreign Keys on a DB

Introduction

This article explains how to rename automatically every relation in your database.It could be useful if your database was upgraded from a different DBMS and the relation names are meaningless (like the Access upgrade does) or if those names have been created in years from different developers using different standards or if you renamed one or more table in your database and you need to fix foreign keys' names also.

Background :

The idea (and the underlying algorithm) is simple:

Take all the relations in the database, look at the tables involved in the relation and give each one the name "FK_ParentTable_ForeignTable[Counter]".
With previous versions of SQL Server it was easier because the user could directly update (with a single statement) system catalogues but in SQL Server 2005 this feature was disabled for consistency reasons.

In SQL Server 2005 there are a lot of useful views lying over the system catalogues that let the user know about everything in every database. The code uses those views to accomplish the task.

Using the code:

The code is just a T-SQL block of code so you can Paste it in a "Management Studio" window and run it from there.Put it as a Stored Procedure body to call when needed.
Run from within a "database update" script...do whatever you would do to run a sql batch.

Points of Interest

This code makes use of some new SQL Server 2005 features.

To make the code simpler it was divided logically using Common table expressions (CTE).Moreover to count properly the foreign keys a ranking function is used.
So if you are new to these you can learn something :)

In depth look

The logic is simple: obtain a list of actual foreign keys on a DB and rename them using the sp_rename extended procedure. So the code is basically a query wrapped around a procedure code that loops on the result set and do the rename work. There's nothing important/special/difficult to point on the procedure.. the interesting part is the query that is explained in detail below.

First of all we need to obtain every foreign key present in our database.
The view sys.foreign_key_columns has the information on "what column is linked to what other column". We use this view to have the list of every distinct relation (a relation could take more than one column). The first CTE has this information.

Next we should translate object IDs into object names.This can be done joining the first CTE with the sys.objects view Additionally we can count how many times a parent is related to a referenced table.This CTE stores: the actual relation name, the parent table, the referenced table and the counter.

The third step is to translate the informations obtained in the second step to a more useful thing: Old relation name and New relation name.
The CASE is used to put or omit the counter if there are more than one relation or one only (you can easily modify it if you want different renaming scheme).

The fourth step is used to take in consideration (for the rename process) only the relation names that don't already exist (because maybe someone has already fixed some them manually or they were created with the right name)

Download Source Code

Content From : CodeProject

Oracle - ORA-12535 error

Issue :
I have two databases. One local (in domain ad.xyz.com) and a remote database (domain us.oracle.com). I am logging in to these from a client machine and I do not have DBA access.

I am able to TNSPING and connect using SQL*Plus to both of these databases. I have created a private DBlink in my local DB pointing to the remote DB. However, when I try to refer any object in the remote DB using this DBlink, I get the "ORA-12535: TNS:operation timed out " error. Since I am able to TNSPING and connect to the DBs, the .ora files are correct.

I have referred to all of the articles I could find on the Internet but these did not help in solving the issues. Can you please let me know what I may have missed out?

Solution : (Given By Brian Peasland)

The only thing TNSPING tells you is that the database listener is up and is configured for the SID defined in your tns string. It does not indicate whether or not you can actually connect to the Oracle instance. The most common reason why you are receiving the ORA-12535 error is due to a firewall configuration issue. While the Listener is listening on port 1521, the connection will use a different port. The firewall could be blocking this other port. You may need to work with your network administrators to resolve this issue.

DbXplorer - Oracle Weblogic 10.3 g

We can connect database schema using DbXplorer.
in this article we will learn how to explore databases using the DbXplorer™, a view that provides an intuitive interface for database access through the ORM Workbench. Using the DbXplorer, you can setup a database connection, add and edit data, review the database artifacts, query the data in an existing table or column, and generate object relational mappings.

Create a New Database Connection :

1. Click on the DbXplorer view tab, if it is visible. If not, open the DbXplorer
view by clicking Window > Show View > DbXplorer.
2. Right-click anywhere within the DbXplorer view and select New Connection.



3. In the Add Database Connection wizard, enter a database connection name. The database connection name can be arbitrary and does not have to match the actual name of the database server. Click Next to proceed.


4. In the Add Database Connection dialog, click Add and select the Hypersonic JDBC driver file, \workshop-jpa-tutorial\web\WEB-INF\lib\hsqldb.jar.


5. Click Next

6. In the JDBC Driver Class field click Browse and select org.hsqldb.jdbcDriver.

7. Workshop provides sample Database URL's for some standard databases, which can be accessed from the Populate from database defaults pull down menu. Select HypersonicSQL In-Memory.


8. For database URL jdbc:hsqldb:{db filename}, specify the Hypersonic database script file location for {db filename}: \workshop-jpa-tutorial\web\hsqlDB\SalesDB .

9. For User, enter sa.



10. Click the Test Connection button to verify the connection information.


11. Click Finish. The new database connection displays in the DbXplorer view.


After we can navigate trough all the tables which is avialable in database.

DBXaminar helps you to see the ralationship between the table.
Ex:

Database Two Phase Commit

Since the 1980s, two phase commit technology has been used to automatically control and monitor commit and/or rollback activities for transactions in a distributed database system. Two phase commit technology is used when data updates need to occur simultaneously at multiple databases within a distributed system. Two phase commits are done to maintain data integrity and accuracy within the distributed databases through synchronized locking of all pieces of a transaction.

Two phase commit is a proven solution when data integrity in a distributed system is a requirement. Two phase commit technology is mostly used for hotel and airline reservations, stock market transactions, banking applications, and credit card systems.

Applying two phase commit protocols ensures that execution of data transactions are synchronized, either all committed or all rolled back (not committed) to each of the distributed databases.

When dealing with distributed databases, such as in the client/server architecture, distributed transactions need to be coordinated throughout the network to ensure data integrity for the users. Distributed databases using the two phase commit technique update all participating databases simultaneously.

Two phase commit has two distinct processes that are accomplished in less than a fraction of a second:

1. The Prepare Phase, where the global coordinator (initiating database) requests that all participants (distributed databases) will promise to commit or rollback the transaction. (Note: Any database could serve as the global coordinator, depending on the transaction.)
2. The Commit Phase, where all participants respond to the coordinator that they are prepared, then the coordinator asks all nodes to commit the transaction. If all participants cannot prepare or there is a system component failure, the coordinator asks all databases to roll back the transaction.