How to Install SQL*Plus Client on Linux

Its really very simple steps to do this, and very crazy while dependency for compatibility with OS (x32 bit or x64 bit).

I have done for x64 bit Linux UBUNTU. Generally Oracle provides .rpm packages , and you need to download those packages into  your machine.

Download here.

Assuming that you have already installed Oracle Database , else you are connecting some remote database. Because, this post only show the sqlplus installation to connect existing database.

Downloaded files :-

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm
Copy these files to your preferred location. I have  used below location.

/usr/java

Now time for convert these .rpm packages into Debian specific package and install on your Ubuntu. Use alien command to convert.

Follow the below steps and install one-by-one.

First install the sqlplus :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm

[sudo] password for dev:

dpkg --no-force-overwrite -i oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-sqlplus.

(Reading database ... 226951 files and directories currently installed.)

Unpacking oracle-instantclient12.1-sqlplus (from oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-sqlplus (12.1.0.1.0-2) ...

Second install the basic (packages) :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
dpkg --no-force-overwrite -i oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb

Selecting previously un-selected package oracle-instantclient12.1-basic.

(Reading database ... 226963 files and directories currently installed.)

Unpacking oracle-instantclient12.1-basic (from oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-basic (12.1.0.2.0-2) ...

Processing triggers for libc-bin ...

ldconfig deferred processing now taking place 

Third install the devel :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

dpkg --no-force-overwrite -i oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-devel.

(Reading database ... 226981 files and directories currently installed.)

Unpacking oracle-instantclient12.1-devel (from oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-devel (12.1.0.2.0-2) ...

Once these installation complete, you can start sqlplus using below command. Either you can use your specific user/password with correct server address.

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

Good to go if no error, else follow with fixing below !! Good luck.

Note - You may face the below library missing issue while executing sqlplus command. Error below :-

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory


Now this issue saying the lib is not loading, the sqlplus is complaining about the missing library.

So you need to edit the oracle.conf and provide the correct lib path (i.e : /usr/lib/oracle/12.1/client64/lib).Add this path into oracle.conf file.

dev@javadevelopersguide-developer-desktop /usr/java $ sudo vi /etc/ld.so.conf.d/oracle.conf

/usr/lib/oracle/12.1/client64/lib

Now, you should be able to connect to your preferred Database. I have connected to our remote server as below :

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus my_user/my_password@z2customdb01.ztest/z2custom.zenv.mycompanyadd.com.in
SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 23 16:30:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Wed Dec 23 2015 16:18:22 +11:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning option
SQL>

SQL>

How to Generate SQL INSERT Statements from Existing Data in Oracle

Use this following format/query for generate script for insert data into database (Table) .When you export data or migrate data from one database to other database it may help you.With out creating the dumps you can export data from one database to other database.But it is table wise.

You can simply generate script for insert  and then run the generated script on command line.

Example 1 :-

 SELECT 'INSERT INTO DUAL VALUES ('''||dummy||''');' FROM DUAL;

Generated Output: INSERT INTO DUAL VALUES ('X');

Example 2 :-

SELECT 
    'INSERT INTO EMP_DETAILS VALUES (' ||
    '''' || REPLACE(EMP_NAME, '''', '''''') || ''', ' ||
    '''' || EMP_SEX || ''', ' ||
    '''' || TO_CHAR(EMP_JOIN_DT, 'DD-MON-YYYY') || ''');' AS insert_script
FROM M_EMPLOYEE;


Generated Output:

INSERT INTO EMP_DETAILS VALUES ('Rakesh', 'M', '03-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Manoj Kumar', 'M', '06-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Santosh Kumar', 'M', '02-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Rakesh Kumar', 'M', '05-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Sunil Dev', 'M', '01-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Sheeba', 'F', '01-JAN-2005')

Use the generated output in command line and execute.

MySQL: How to Split a String by Delimiter using SUBSTRING_INDEX

String operation with MYSQL database is quite simple. But some times it found very ridiculous to find a expected result.That exactly happens with me. Actually I was expecting the result like below :-

But when I run my query it generates the out put like below:-

But i want only the name before the first comma(,) occurrence like :-

 Jon kumar Pattnaik from first row.

To find the expected result from database I have used SUBSTRING_INDEX(str,delim,index count) method.A string operation method .

Query :-

select SUBSTRING_INDEX(GROUP_CONCAT(s.vchSName,' ',vchMidName,' ',vchLastName),',',1) as nam,vchSGudian,vchSGRelation  from t_emp_details ;


Now its running fine with a good expected result.

SUBSTRING_INDEX(str,delim,index count)

Notes :- Parameters

str- is the main string from which we need to find the substring.
delim- is the separator of main string.
index count- is the number of separator you want to find.

Example:-
You,are,a,programmer.

select SUBSTRING_INDEX( 'You,are,a,programmer',',',1) from myTable;

 

Out put:- You

Here in the above query :-

You,are,a,programmer.--( Main String)
comma (,) -- (Separator or delim)
1 --(Index Count)

Combining Rows in MySQL: A Practical Guide to GROUP_CONCAT()

GROUP_CONCAT() is one the most essential function over many areas of software development.The basic purpose of this function is to concatenate all records/rows/tuples of a single column/field into a single string.MYSQL library provides huge amount of function with huge requirement of clients.

Suppose:-

select vchDay from m_days_list

A column has these following records

After use GROUP_CONCAT() the output like below-

Query :-

select GROUP_CONCAT(vchDay) from m_days_list

GROUP_CONCAT() not only working on single column , but it also working on multiple column/field.And the results are also similar as single column.It gives higher operating value than GROUP BY clause.

select GROUP_CONCAT(vchDay,vchMonth) from m_days_list

Output:-

FridayJanuary,MondayFebruary,SaturDayMarch

MySQL: How to Store Multilingual & Special Character Data Using UTF-8

Every now and then, client requirements demand storing multilingual text, Arabic scripts, or special characters in a MySQL database. If the database encoding isn't configured properly, you end up with garbled text or question marks (???).

I ran into this exact issue recently on a client project. The fix is straightforward once you configure the right character set and collation.

Here is how to set up your tables and columns to handle multilingual data properly, either through a GUI or direct SQL queries.

Method 1: Using MySQL GUI (Workbench / phpMyAdmin)

1. Table Collation: Set the table collation to utf8 (or utf8mb4) with utf8_bin (or utf8mb4_bin).

2. Column Collation: Ensure each text column (VARCHAR, TEXT) is explicitly set to use utf8 character set and utf8_bin collation.

3. Save Changes: Apply the updates to your schema.

• [utf-8-table-setting.jpg] – Setting table-level collation

• [utf-8-to-column.jpg] – Setting column-level collation

• [data-in-table.jpg] – Multilingual data successfully stored inside the table

Method 2: Using SQL Queries

If you prefer running SQL scripts directly or need to alter existing tables, use the queries below.

Set Collation at Column and Table Creation

CREATE TABLE example_multilingual (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
    notes TEXT CHARACTER SET utf8 COLLATE utf8_bin
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

Alter Existing Columns

If your table already exists and you need to update existing columns to support special character sets:

 ALTER TABLE `admin_kairali`.`t_report_language` 
    CHANGE COLUMN `vchBeing` `vchBeing` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchScrtry` `vchScrtry` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchAccount` `vchAccount` VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
    CHANGE COLUMN `vchReceiversign` `vchReceiversign` VARCHAR(4000) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL;

 

Quick Tip: For modern MySQL setups (MySQL 5.7+ and MySQL 8.0+), consider using utf8mb4 with utf8mb4_unicode_ci or utf8mb4_bin instead of standard utf8, as utf8mb4 fully supports 4-byte characters like emojis and complex scripts.

 

 

 

MySQL: Find All Foreign Key Constraints in a Database

It was frustrating when I struggled to find all the foreign keys in my MySQL database. My project manager kept asking for them, so when I finally figured it out, it was a huge relief and a great moment!

While this is straightforward in MS SQL Server, doing it in MySQL can be tricky if you don't know where to look. I'm sharing this snippet here to help other developers quickly pull this information.

Run these queries in your MySQL query editor:

1. Find Foreign Keys for a Specific Schema (Database) and Table

 

SELECT 
    f.table_schema AS 'schema',
    f.table_name AS 'table',
    f.column_name AS 'column',
    f.constraint_name AS 'constraint_name',
    f.referenced_table_name AS 'referenced_table',
    f.referenced_column_name AS 'referenced_column'
FROM 
    information_schema.KEY_COLUMN_USAGE f
WHERE 
    f.table_schema = 'admin_kairali' 
    AND f.referenced_column_name IS NOT NULL; 

 

 Note: If you want to filter down to a single specific table, add AND f.table_name = 'your_table_name' to the WHERE clause.

2. Find All Foreign Keys Across the Entire Server

 

SELECT 
    f.table_schema AS 'schema',
    f.table_name AS 'table',
    f.column_name AS 'column',
    f.constraint_name AS 'constraint_name',
    f.referenced_table_name AS 'referenced_table',
    f.referenced_column_name AS 'referenced_column'
FROM 
    information_schema.KEY_COLUMN_USAGE f
WHERE 
    f.referenced_column_name IS NOT NULL;

 

 

MySQL Security: SQL Injection and Password Security

When developing applications with MySQL, security should be considered carefully, especially when handling login credentials and user input.

One common security problem is SQL Injection. It can occur when an application directly adds user input to an SQL query instead of using parameterized queries or prepared statements.

Developers should be careful when creating login or validation queries, especially when user input is directly added to an SQL query.

Anyone using MySQL in an application should be aware of common security mistakes, especially when handling login credentials.

For example, a login query may contain a condition like:

WHERE BINARY pass = 'yourpassword'

The WHERE clause itself is not the security problem. The problem occurs when an application directly adds user input to the SQL query.

If user input is concatenated directly into an SQL statement, an attacker may be able to manipulate the SQL condition and bypass the intended validation. This is known as SQL Injection.

Therefore, do not construct SQL queries by directly concatenating username, password, or other user-provided values. Always use parameterized queries or prepared statements.

For example, instead of creating an SQL statement by adding the username and password directly to the query, the application should use a prepared statement and pass these values as parameters.

This keeps the SQL statement separate from the user-provided values and helps prevent SQL Injection.

Password Security

Password security is also an important part of application security.

Do not store application passwords as plain text. Also, do not use HEX() or MD5() as a replacement for proper password security.

HEX() only converts data into hexadecimal representation. It does not provide password protection.

MD5 is also not suitable for securely storing passwords. Passwords should be stored using a proper password-hashing mechanism provided by the application framework or security library.

Some MySQL Security Best Practices

Some basic security practices to follow when working with MySQL are:

1. Always use parameterized queries or prepared statements.

2. Never store application passwords as plain text.

3. Do not store database credentials directly in source code.

4. Avoid storing passwords or sensitive information in application logs.

5. Give database users only the permissions they require.

6. Use secure connections where required.

7. Keep MySQL and related software updated.

8. Avoid exposing the MySQL server directly to the public Internet unless it is properly secured.

Always follow the official MySQL security documentation when implementing security for your application and database.

MySQL: Create Database Backup Using a Windows Batch File

Creating a complete solution in MySQL can sometimes be challenging, especially when you are looking for a specific administration or backup requirement.

I faced this situation several times while working with my development team. One of my colleagues asked me how to create MySQL database dumps using a batch file so that the backup could be executed easily from Windows.

I had already written about creating MySQL dumps using the command line, but this time I needed to automate the process using a Windows batch file.

After preparing the batch file and testing it successfully, I thought it would be useful to share the approach here.

Create the Batch File

Create a new file with a .bat extension, for example:

mysql_backup.bat

Add the following commands to the file:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -hlocalhost -uroot -p testDB1 > D:\mybackupdumps.sql

exit

Replace the following values according to your environment:

• localhost — MySQL server host
• root — MySQL username
• testDB1 — database name
• D:\mybackupdumps.sql — location and name of the backup file

When the batch file runs, mysqldump will prompt for the MySQL password.

Note: Avoid putting the MySQL password directly in the batch file because the file may be accessible to other users or applications.

Backup a Database on a Remote Server

You can also specify the MySQL server hostname or IP address using the -h option:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -h192.168.1.100 -umyuser -p mydatabase > D:\mybackup.sql

exit

Replace the host, username, database name, and output path with your actual values.

Backup All Databases

If you want to create a dump containing all databases accessible to the MySQL user, use the --all-databases option:

cd "C:\Program Files\MySQL\MySQL Server\bin"

mysqldump -hlocalhost -umyuser -p --all-databases > D:\myalldatabases.sql

exit

Useful mysqldump Options

mysqldump provides many options that can be used to control how the dump is created. Some commonly used options include:

--add-locks
Adds LOCK TABLES and UNLOCK TABLES statements around table dumps.

--all-databases
Dumps all databases.

--comments
Includes comments in the dump file.

--compact
Produces a more compact dump output by reducing some additional statements and comments.

--ignore-table=db_name.table_name
Excludes the specified table from the dump.

 

Final Note

A Windows batch file makes it easier to repeat a database backup command without manually typing it each time. It can also be used as part of a scheduled backup process using Windows Task Scheduler.

The exact MySQL installation path may differ depending on the MySQL version and how it was installed, so update the cd path accordingly.

MySQL: Create a Database Backup Using mysqldump

When working with MySQL, there may be situations where you need to create a backup or dump file of your database.

The mysqldump utility can be used to export a MySQL database into a SQL dump file. The dump can then be used to restore the database on another MySQL server.

Step 1: Open the MySQL bin Directory

Go to the bin directory of your MySQL installation.

For example, on Windows:

C:\Program Files\MySQL\MySQL Server\bin

The exact path may be different depending on your MySQL version and installation location.

Step 2: Run the mysqldump Command

Open Command Prompt and run:

mysqldump -u your_username -p your_databasename > D:\mybackup.sql

You will be prompted to enter the MySQL password.

For example:

mysqldump -u root -p testdb > D:\mybackup.sql

Step 3: Check the Backup File

After the command completes successfully, check the specified location:

D:\mybackup.sql

The file contains the SQL statements required to recreate the database objects and data.

Important Note

The mysqldump command should be run against a MySQL server compatible with the dump format. When moving a database between different MySQL versions or environments, it is a good idea to test the dump by restoring it on the target server before relying on it as a production backup.

Java MySQL: Get the Last Inserted ID Using JDBC

I have faced many questions and problems related to retrieving the ID of the last inserted record when using MySQL as the backend.

When a table has an auto-increment primary key, we may need to retrieve the generated ID immediately after inserting a record.

In Java JDBC, this can be done using Statement.RETURN_GENERATED_KEYS and getGeneratedKeys().

Example

String query = "INSERT INTO m_time_list " +
               "(vchTime, vchGMT, createdDate) " +
               "VALUES (?, ?, CURDATE())";

PreparedStatement pstmt =
        connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

pstmt.setString(1, form.getTxtTime());
pstmt.setString(2, form.getRdbgmt());

pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys();

int sid = 0;

if (rs.next()) {
    sid = rs.getInt(1);
}

System.out.println("Generated ID: " + sid);

After the insert is executed, sid contains the generated auto-increment ID.

Using MySQLLAST_INSERT_ID()

MySQL also provides the LAST_INSERT_ID() function for obtaining the automatically generated ID.

SELECT LAST_INSERT_ID();

For Java applications using JDBC, however, Statement.RETURN_GENERATED_KEYS with getGeneratedKeys() is generally a convenient way to retrieve the generated key directly from the insert operation.

Note: Use PreparedStatement parameters rather than concatenating user-provided values directly into an SQL string. This helps avoid SQL injection and also handles parameter values correctly.

MySQL: Generate Fixed-Length Numbers with Leading Zeros

There may be situations where you need to display a number from the database in a specific format.

For example, you may want to display a number from 001 to 999 instead of 1 to 999.

The following MySQL query can be used to add leading zeros to a number.

MySQL Query

SELECT LPAD(intfield_id, 3, '0') AS columnname
FROM test;

For example:

intfield_id	Result
1	001
9	009
25	025
100	100
999	999

The LPAD() function adds characters to the left side of a string until it reaches the specified length.

In this example:

LPAD(intfield_id, 3, '0')

means that the value should have a total length of 3 characters, and 0 should be added to the left when necessary.

This can be useful when a database value needs to be displayed as a fixed-length number, such as 001, 002, 003, and so on.

SQL Server: Error Handling Using TRY...CATCH and Transactions

Error handling is an important part of database programming. When an operation fails because of a foreign key violation, duplicate key, or another database error, it is useful to return a meaningful error message to the application.

The following example demonstrates how to use TRY...CATCH, transactions, ERROR_NUMBER(), and RAISERROR to handle errors in a stored procedure. The example also shows how a foreign key dependency can prevent a record from being deleted.

Create the Primary Table

Create a table named TEST1 with two fields: T_ID and T_NAME.

CREATE TABLE TEST1
(
    T_ID INT IDENTITY(1,1) PRIMARY KEY,
    T_NAME VARCHAR(25)
);
GO

Create the Dependent Table

Create another table named TEST2_DEPEND with two fields: TD_ID and TD_ADDRESS.

CREATE TABLE TEST2_DEPEND
(
    TD_ID INT FOREIGN KEY REFERENCES TEST1(T_ID),
    TD_ADDRESS VARCHAR(25)
);
GO

Insert Records into TEST1

INSERT INTO TEST1 (T_NAME)
VALUES ('Rakesh');
GO

INSERT INTO TEST1 (T_NAME)
VALUES ('Kumar');
GO

INSERT INTO TEST1 (T_NAME)
VALUES ('Bardhan');
GO

Create a Stored Procedure for Insert and Delete

The following stored procedure accepts an operation type:

• I — Insert

• D — Delete

The procedure uses a transaction so that changes can be rolled back if an error occurs.

ERROR_NUMBER() is used to identify the error raised by SQL Server. For example, error 547 is commonly associated with a foreign key constraint violation.

CREATE PROCEDURE USP_TEST1
(
    @OP_TYP VARCHAR(3),
    @ID INT,
    @ADDRESS VARCHAR(30) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF @OP_TYP = 'I'
        BEGIN
            INSERT INTO TEST1 (T_NAME)
            VALUES (@ADDRESS);
        END
        ELSE IF @OP_TYP = 'D'
        BEGIN
            DELETE FROM TEST1
            WHERE T_ID = @ID;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @ERROR_NUMBER INT;

        SET @ERROR_NUMBER = ERROR_NUMBER();

        IF XACT_STATE() <> 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        IF @ERROR_NUMBER = 547
        BEGIN
            RAISERROR(
                'Cannot delete! The value has a dependency in another table.',
                16,
                1
            );
        END
        ELSE IF @ERROR_NUMBER = 2627
        BEGIN
            RAISERROR(
                'Cannot insert! A duplicate value already exists.',
                16,
                1
            );
        END
        ELSE
        BEGIN
            RAISERROR(
                'An error occurred while processing the database operation.',
                16,
                1
            );
        END
    END CATCH
END;
GO

Insert a Value into the Foreign Key Table

The following statement creates a dependency on the record with T_ID = 2.

INSERT INTO TEST2_DEPEND (TD_ID, TD_ADDRESS)
VALUES (2, 'Banaglore');
GO

Execute the Procedure

Now try to delete the record with T_ID = 2 from the primary table.

EXEC USP_TEST1 'D', 2;

Because TEST2_DEPEND contains a record that references TEST1.T_ID = 2, SQL Server raises a foreign key violation.

Expected Output

Cannot delete! The value has a dependency in another table.

The transaction is rolled back, so the failed delete does not change the data.

Checking SQL Server Error Messages

SQL Server provides system views that can be used to view available error messages and language information.

SELECT *
FROM sys.messages;

SELECT *
FROM sys.syslanguages;

Remarks

This example demonstrates how database errors can be handled inside a stored procedure and converted into more meaningful messages for the application.

Using transactions together with TRY...CATCH helps maintain data consistency when an operation fails. The ERROR_NUMBER() function can be used to identify the specific SQL Server error and take appropriate action.

For new development, THROW is generally preferred over RAISERROR for re-throwing or generating errors. This example uses RAISERROR because it demonstrates the approach used in the original article.

SQL Server: Adding IDENTITY Property to an Existing Column

Adding the IDENTITY property to an existing column in SQL Server is not as straightforward as changing the column data type. SQL Server does not provide a simple ALTER COLUMN statement to add the IDENTITY property to an existing column.

One way to accomplish this is to create a new IDENTITY column, remove the old column, and then rename the new column with the original column name. The following example shows the steps.

Steps to Add the IDENTITY Property

• Add a new column with the `IDENTITY` property. 
• Drop the constraint, if any.
•  Drop the old column.
• Rename the new column with the old column name.

Existing Table

CREATE TABLE Employee
(
Emp_Id INT PRIMARY KEY,
Emp_Name VARCHAR(100)
); 

1. Add a New Column with IDENTITY

ALTER TABLE Employee
ADD EmpNew_ID INT IDENTITY(1,1);

2. Drop the Primary Key Constraint

If the existing column is a primary key, first drop the primary key constraint.

ALTER TABLE Employee
DROP CONSTRAINT PK__Employee__262359AB07020F21;

Note: The constraint name above is an example. The actual constraint name may be different in your database.

3. Drop the Old Column

ALTER TABLE Employee
DROP COLUMN Emp_Id

4. Rename the New Column
 

Rename the new column with the old column name:

EXEC sp_rename 'Employee.EmpNew_ID', 'Emp_Id', 'COLUMN';

Now the table has the new `IDENTITY` column with the original column name. 

Remarks

I came across this approach through one of my co-workers. At first, I thought it would be as simple as altering the existing column, but after looking into it, I realized that adding the `IDENTITY` property to an existing column requires a different approach.

The above method works by creating a new column with the `IDENTITY` property, removing the old column, and then renaming the new column.

Before using this approach, make sure you understand the impact on existing data and dependencies. If the table contains data, dropping the old column will remove that data. Also, consider any primary keys, foreign keys, indexes, constraints, triggers, and other objects that depend on the old column.

If necessary, drop or recreate the required constraints and dependent objects as part of the change.
 

Display the Table

SELECT *
FROM Employee;

SQL Server: Rename Columns or Tables Using SP_RENAME

Renaming a table or column is a common requirement when working with SQL Server. In SQL Server, the `sp_rename` procedure can be used to rename tables and columns. This article shows how to rename a column, rename a table, and change the definition of a column using `ALTER TABLE`

Rename a Column

To rename a column in SQL Server, use `sp_rename` with the `COLUMN` object type.


EXEC sp_rename 'TABLE_NAME.COLUMN_NAME', 'NEW_COLUMN_NAME', 'COLUMN';


For example:


EXEC sp_rename 'dbo.Employee.EMP_NAME', 'EMPLOYEE_NAME', 'COLUMN';

The schema name can be included in the existing column name, which is recommended when specifying the object.
 

Change the Data Type or Size of a Column

`ALTER TABLE ... ALTER COLUMN` is used to change the definition of a column. It does **not** rename the column.

For example:


ALTER TABLE Employee
ALTER COLUMN EMP_NAME VARCHAR(100);

 Rename a Table

To rename a table, use `sp_rename`:

EXEC sp_rename 'dbo.Employee', 'New_Employee';

The existing table name should include the schema when appropriate, while the new table name should be specified without the schema.

 About sp_rename

`sp_rename` changes the name of a user-created object in the current database. It can be used to rename tables, columns, indexes, and other supported objects.

 Remarks

Changing any part of an object name can break scripts and stored procedures. Microsoft recommends not using `sp_rename` to rename stored procedures, triggers, user-defined functions, or views. Instead, drop the object and re-create it with the new name.

Renaming a table or column does not automatically update references to that object. Any dependent queries, views, triggers, stored procedures, or other objects may need to be updated manually. You can use `sys.sql_expression_dependencies` to identify dependencies before renaming an object.

To rename objects, columns, and indexes, you need `ALTER` permission on the object. To rename user-defined types, `CONTROL` permission on the type is required. Renaming a database requires membership in the `sysadmin` or `dbcreator` fixed server roles.

SQL: Fetching Records One-by-One Without Using a Cursor

Fetching Records One-by-One Without Using a Cursor

The following example shows how to process records one-by-one without using a cursor.

DECLARE @EMP_ID CHAR(11);

SET ROWCOUNT 0;

SELECT *
INTO #MYTEMP
FROM Employee;

SET ROWCOUNT 1;

SELECT @EMP_ID = EMP_ID
FROM #MYTEMP;

WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0;

SELECT *
FROM #MYTEMP
WHERE EMP_ID = @EMP_ID;

DELETE FROM #MYTEMP
WHERE EMP_ID = @EMP_ID;

SET ROWCOUNT 1;

-- Set one ID to @EMP_ID from #MYTEMP
SELECT @EMP_ID = EMP_ID
FROM #MYTEMP;
END;

SET ROWCOUNT 0;

Remarks

Cursors are generally avoided when they are not required because row-by-row processing can be expensive compared with set-based operations.

In the above example, a temporary table is used to process the records one-by-one without explicitly declaring a cursor.

`SET ROWCOUNT 0` allows statements to process all applicable rows, while `SET ROWCOUNT 1` limits the number of rows processed by the statement to one.

The query uses this behavior to retrieve one `EMP_ID` at a time, process that record, delete it from the temporary table, and then retrieve the next `EMP_ID`.

This approach can be useful for specific requirements where records need to be processed individually without using a cursor.
 

Note: SET ROWCOUNT should be used with caution in new development. Microsoft recommends using TOP or OFFSET/FETCH where appropriate to limit the number of rows. Microsoft also advises against using SET ROWCOUNT with DELETE, INSERT, and UPDATE in new development.

SQL Server: Make Your Database Read-Only or Read-Write

Sometimes you may need to make a SQL Server database read-only for administrative or maintenance purposes.

Before setting a database to read-only, make sure that no active operations or users require write access to the database.

Using ALTER DATABASE

To set a database to read-only:

ALTER DATABASE TestDB1 SET READ_ONLY;

Once the database is read-only, users can query the existing data, but write operations such as INSERT, UPDATE, and DELETE are not allowed.

If the database was set to read-only temporarily and you want to allow write operations again, use:

ALTER DATABASE TestDB1 SET READ_WRITE;Using SP_DBOPTION

Older versions of SQL Server provided the sp_dboption system stored procedure for changing database options.

For example:

EXEC sp_dboption 'TestDB1', 'READ ONLY', 'TRUE';

However, sp_dboption is deprecated. For current SQL Server versions, it is recommended to use ALTER DATABASE instead.

Denying INSERT and UPDATE Permissions

 Another approach is to prevent specific users or roles from modifying particular tables rather than making the entire database read-only.

For example:

DENY INSERT, UPDATE ON dbo.TableName TO UserName;

The DENY permission takes precedence over a corresponding GRANT, so this approach can be useful when you want users to have access to the database but prevent modifications to specific tables.

Choose the approach based on your requirement:

• Use ALTER DATABASE ... SET READ_ONLY when the entire database should be read-only.

• Use DENY when only specific users or tables need to be protected from modifications.

MySQL: LIMIT Not Working as Expected

I have a Case Study!

I received a question about why the MySQL LIMIT clause was not working as expected. However, the behavior was correct according to the MySQL documentation.

The LIMIT clause can be used in the following format:

LIMIT offset, row_count

The first parameter specifies the starting position. The first record starts at position 0.

The second parameter specifies the number of records to return, not the position of the last record.

Example 1

LIMIT 0, 10

This returns 10 records starting from position 0, that is, positions 0 through 9.

Example 2

LIMIT 10, 20

This returns 20 records starting from position 10, that is, positions 10 through 29.

Example 3

LIMIT 10, 10

This returns 10 records starting from position 10, that is, positions 10 through 19.

For example:

SELECT *
FROM dbName.emp
LIMIT 10, 10;

The query returns 10 records, starting from the record at position 10.

SQL Server: Find the Number of Columns in a Table

The following function can be used to find the number of columns in a specified table. The function returns an INT value, so it is a scalar-valued function.

Create the Function

-- Function for finding the number of columns in a given table

CREATE FUNCTION FUN_COL_COUNT(@T_NAME VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @CNT INT;

    SELECT @CNT = MAX(ORDINAL_POSITION)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @T_NAME;

    RETURN @CNT;
END

Run the Function

SELECT dbo.FUN_COL_COUNT('Table_Name');

The above statement returns the number of columns in the specified table.

Here, `dbo` represents the database schema under which the function is created.

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view provides information about the columns in tables and views in the database. It is part of the INFORMATION_SCHEMA views provided by SQL Server and follows the SQL standard for metadata access.

The `ORDINAL_POSITION` column indicates the position of each column within the table. The function uses the maximum `ORDINAL_POSITION` value to determine the number of columns.

Note: This example uses `INFORMATION_SCHEMA.COLUMNS` for metadata access. When working specifically with SQL Server, the `sys.columns` catalog view is another commonly used option. 

SQL: Script to Drop All Foreign Key Constraints from the Current Database

If you need to drop all foreign key constraints from the current SQL Server database, you can generate the required ALTER TABLE statements using the following query.

Generate the Script

SELECT
    'ALTER TABLE ' + OBJECT_NAME(F.parent_object_id)
    + ' DROP CONSTRAINT ' + F.name
FROM sys.foreign_keys F;

Run the above query. It will generate ALTER TABLE statements for all foreign key constraints in the current database.

Example Output

ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_PTCD
ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_TNID
ALTER TABLE COM_MST DROP CONSTRAINT FK__COM_MST__COM_TPC

Copy the generated statements and run them to drop the foreign key constraints. This script can be useful when you need to temporarily remove foreign key constraints, such as during database maintenance, data migration, or when you need to modify and recreate constraints.

I came across this requirement while working on a project when my Project Manager asked me to find a way to drop all constraints and recreate them later. Working through the problem was a good database-learning experience and helped me understand how SQL Server stores and exposes foreign key constraint information.

MySQL: Use of the LIMIT Keyword

MySQL: Use of the LIMIT Keyword

The LIMIT keyword is used to restrict the number of rows returned by a MySQL query. There are several situations where LIMIT can help MySQL reduce unnecessary processing.

1. LIMIT with ORDER BY

If you use LIMIT row_count with ORDER BY, MySQL can stop sorting once it has found the required number of rows instead of sorting the complete result. If the ordering can be done using an index, this can be very fast.

2. LIMIT with DISTINCT

When LIMIT is combined with DISTINCT, MySQL stops processing once it finds the required number of unique rows.

3. LIMIT with GROUP BY

In some cases, GROUP BY can be resolved by reading the key in order and calculating the required summaries. LIMIT can prevent MySQL from calculating unnecessary GROUP BY values.

4. LIMIT and Query Execution

Once MySQL has sent the required number of rows to the client, it can stop processing the query unless SQL_CALC_FOUND_ROWS is being used.

5. LIMIT 0

LIMIT 0 quickly returns an empty result set. This can be useful for checking whether a query is valid.

Example

SELECT * FROM dbName.emp LIMIT 0;

LIMIT 0 can also be useful with MySQL APIs when you need to determine the types of the result columns.

Example

SELECT * FROM dbName.emp LIMIT 10;

The above query retrieves only 10 records from the emp table.

Sometimes we need to retrieve records in batches. For example, if the emp table contains 100 records and we want to retrieve 10 records at a time, we can use LIMIT with an offset.

SELECT * FROM dbName.emp LIMIT 0, 10;

SELECT * FROM dbName.emp LIMIT 10, 10;

SELECT * FROM dbName.emp LIMIT 20, 10;

The first value specifies the starting position, and the second value specifies the number of records to return.

Case Study: Understanding LIMIT

I came across a question where someone felt that LIMIT was not working correctly. The issue was actually with understanding the meaning of the two parameters.The first parameter indicates the starting record. 

The first record starts at position 0. The second parameter specifies the number of records to return, not the ending record.

For example:

LIMIT 0, 10

This returns 10 records starting from position 0, which corresponds to records 0 through 9.

LIMIT 10, 20

This returns 20 records starting from position 10, which corresponds to records 10 through 29.

LIMIT 10, 10

This returns 10 records starting from position 10, which corresponds to records 10 through 19.

Example:

SELECT * FROM dbName.emp LIMIT 10, 10;

In short, remember:

LIMIT offset, row_count

The first value is the starting position, and the second value is the number of rows to return.