I’m working with a database table and need to find out the position numbers of specific columns. I tried using some SQL functions but I’m not getting the right results.
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employee'
ORDER BY ORDINAL_POSITION
This query doesn’t seem to work as expected. I want to get the index or position number for each column in my table. Is there a better way to do this? I need this information to reference columns by their position rather than name in my application.
your query looks fine. check if ur using the right db schema - try adding TABLE_SCHEMA = 'your_db_name' to the where clause. also, some dbs use different system tables. in sql server, sys.columns works better sometimes.
what db are u working with? the INFORMATION_SCHEMA method is usually good, but maybe there’s a quirk in yours. wat error pops up when u run that query? no results, or just wrong data?
Your query looks right for most databases. I encountered a similar issue with PostgreSQL where case sensitivity was the culprit. I recommend wrapping your table name in quotes according to your database’s requirements. Additionally, specify the schema explicitly in your WHERE clause. For instance, in PostgreSQL, using WHERE table_name = 'employee' AND table_schema = 'public' with lowercase table names resolved my issue. Some databases also require appropriate permissions to access INFORMATION_SCHEMA views. If you continue to face difficulties, consider using database-specific alternatives such as PRAGMA table_info(table_name) for SQLite or querying sys.columns joined with sys.tables for SQL Server.