How To Find A Column In Sql

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

Step i: Create a Database. For this utilise the below control to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step ii: Use the GeeksForGeeks database. For this utilize the below command.

Query:

USE GeeksForGeeks

Output:

Step iii: Create a table of EVALUATION inside the database GeeksForGeeks. This tabular array 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 field 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 tabular array EVALUATION.

Query:

EXEC SP_COLUMNS EVALUATION;

Output:

Footstep five: To find the tables and cavalcade names having a common prefix, nosotros need to run a query as follows. The post-obit query searches all columns in the database by comparing the column names with the provided prefix. And so, the resulting tables and columns are listed and ordered by tabular array names. We use keywords similar WHERE Like and ORDER BY to reach this. The following query searches for columns whose names start with 'Educatee'.

Syntax:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME Similar 'PREFIX%' Lodge By TABLE_NAME;

Query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME Similar 'STUDENT%' Order Past TABLE_NAME;

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

Output:

Step six: The following query searches for columns whose names showtime 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:

Pace 7: The post-obit query searches for columns whose names start with 'Scientific discipline'.

Query:

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

Notation – All columns returned have a prefix of 'Science' in their names.

Output:

Source: https://www.geeksforgeeks.org/how-to-search-for-column-names-in-sql/

Posted by: santanafaccons.blogspot.com

Share this post