CODEWITHSUNDEEP
sqloracle-database
VK Chikkadamalla
VK Chikkadamalla

Reputation: 239

how to get column names for the particular table using sql query

I am facing an issue when fetching column names for the particular table

I wrote the following query

SELECT column_name 
FROM all_tab_cols 
WHERE  Table_name like 'tabelname';

But what happen in table have 54 columns but when I run that code 224 columns coming. I don't known why.

Upvotes: 0

Views: 495

Answers (2)

VK Chikkadamalla
VK Chikkadamalla

Reputation: 239

SELECT column_name FROM all_tab_cols WHERE table_name = 'VERIFICATION' AND owner = 'APPDB' AND column_name NOT IN ( 'password', 'version', 'id' )

Upvotes: 0

user330315
user330315

Reputation:

Assuming Oracle, not SQL Server.

all_tabl_cols shows columns from all tables the current user has access to, not those owned by the current user. If the same table exists in different schema, all of them will be shown.

The LIKE operator makes no sense if you don't use a wildcard pattern with it. It would be cleaner to use Table_name = 'tabelname' instead.

Oracle stores table names in UPPERCASE (unless you created them using those dreaded double quotes). And string comparison is case-sensitive in Oracle. So you must compare the the content of the column table_name with an uppercase name-

You can either use user_tab_cols instead of all_tab_cols

SELECT column_name 
FROM user_tab_cols 
WHERE table_name = 'THE_TABLE';

or you need to specify the owner:

SELECT column_name 
FROM all_tab_cols 
WHERE table_name = 'THE_TABLE'
  AND owner = user;

Upvotes: 3

Related Questions