Checking to see if a table, sequence, or any object exists

As a DBA there are times when you need to determine if a table, view, sequence or any other object exists withing your database structure. Since there are a few versions of your software either in production or out in the field.

As an example lets say you have a database in the production and you are working on a new release. Programmers need changes are made to the database schema to support new requirements, such as adding a new column to a table or altering the length of a column. As a result you need to write update scripts to be run against the database currently in production. Since the changes are minor running updates on database is much better than rebuilding saving time and money.

Using PL/SQL blocks you can query the data dictionary to determine if the table exists. If it is already there simple issue the alter table command via DBMS_UTILITY.EXEC_DDL_STATEMENT to modify the table accordingly. If the table does not exist, create the table with the new table structure.

Here is an example of the code that checks to see if the column is NULLABLE, if it is not it sets the column as NULL.

CONNECT SYSTEM/sys+password — connect to the system

DECLARE
L_NULLABLE VARCHAR2(100);
BEGIN
SELECT NULLABLE
INTO L_NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER = ‘YOUR_SCHEMA_NAME’
AND TABLE_NAME = ‘YOUR_TABLE_NAME’
AND COLUMN_NAME = ‘YOUR_COLUMN_NAME’;

IF L_NULLABLE = ‘N’ THEN

DBMS_UTILITY.EXEC_DDL_STATEMENT(‘ALTER TABLE YOUR_SCHEMA_NAME.YOUR_TABLE_NAMEMODIFY (YOUR_COLUMN_NAME NULL)’);
END IF;

END;
/

This script checks for a column but you can just as easily check to a table (DBA_ALL_TABLES), a view (DBA_ALL_VIEWS) or a sequence (DBA_SEQUENCES).

As always you need to test, test, and test again to ensure the scripts are doing what you intend them to do. Especially when you will be running them against a production database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s