CREST Practitioner Security Analyst (CPSA) Practice

In MS-SQL, which command is used to list all columns of a specific table?

1. SELECT * FROM sysobjects

2. SELECT name FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='mytable')

3. SHOW COLUMNS FROM mytable

4. SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

The correct answer is: SELECT name FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='mytable')

The correct choice for listing all columns of a specific table in MS-SQL is indeed notable for its use of system catalog views, which provide useful metadata about tables within the SQL Server environment. The command in this option utilizes the `syscolumns` table, which contains a row for each column of a table or view, and `sysobjects`, which contains a row for each object created in the database, such as tables, views, and procedures. The nested query retrieves the unique identifier (`id`) for the specified table ('mytable') from `sysobjects`, allowing the outer query to find and return all column names associated with that specific table. This method effectively leverages SQL Server’s system objects to retrieve detailed information about the table structure. In contrast, the other options do not directly fulfill the requirement of listing all columns from a specific table in MS-SQL. For example, the first option retrieves general system object information rather than column-specific data. The third option, while it resembles commands used in other SQL dialects such as MySQL, is not applicable within MS-SQL. The fourth option, though it references `INFORMATION_SCHEMA.COLUMNS`, which does contain information about columns, would need a condition to filter for 'mytable