In SQL, sometimes we need to search the column names in a table using the prefixes. For this article, we will be using the Microsoft SQL Server as our database and Select keyword.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table of EVALUATION inside the database GeeksForGeeks. This table has 6 columns namely STUDENT_NAME, STUDENT_ID, ENGLISH_MARKS, ENGLISH_PERCENTAGE, SCIENCE_MARKS, SCIENCE_PERCENTAGE containing the names and ids of students, their marks and percentages in English subject and their marks and percentages in Science subject.

Query:

CREATE TABLE EVALUATION(
STUDENT_NAME VARCHAR(10),
STUDENT_ID INT,
ENGLISH_MARKS INT,
ENGLISH_PERCENTAGE INT,
SCIENCE_MARKS INT,
SCIENCE_PERCENTAGE INT);

Output:

Step 4: Describe the structure of the table EVALUATION.

Query:

EXEC SP_COLUMNS EVALUATION;

Output:

Step 5: To find the tables and column names having a common prefix, we need to run a query as follows. The following query searches all columns in the database by comparing the column names with the provided prefix. Then, the resulting tables and columns are listed and ordered by table names. We use keywords like WHERE LIKE and ORDER BY to achieve this. The following query searches for columns whose names start with ‘STUDENT‘.

Syntax:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'PREFIX%'
ORDER BY TABLE_NAME;

Query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'STUDENT%'
ORDER BY TABLE_NAME;

Note: All columns returned have a prefix of ‘STUDENT‘ in their names.

Output:

Step 6: The following query searches for columns whose names start with ‘ENGLISH‘.

Query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'ENGLISH%'
ORDER BY TABLE_NAME;

Note – All columns returned to have a prefix of ‘ENGLISH‘ in their names.

Output:

Step 7: The following query searches for columns whose names start with ‘SCIENCE‘.

Query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'SCIENCE%'
ORDER BY TABLE_NAME;

Note – All columns returned have a prefix of ‘SCIENCE‘ in their names.

Output: