SQL Column Name wildcard

Question

I have a table with 30+ fields and I want to quickly narrow my selection down to all fields where column name start with 'Flag'.

select * Like Flag% from Table1

Answer 1

You will want to build a dynamic query as explained here: https://stackoverflow.com/a/4797728/9553919

SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Foods'
        AND table_schema = 'YourDB'
        AND column_name LIKE 'Vegetable%'

Answer 2

This SQL Statement should be useful. You may be able to simplify it but it does work.

Edit2: I just now saw your pervasive-sql tag. Unfortunately I've never worked with that and don't know if the syntax is compatible with MS SQL Server. I'll let my answer here in case it helps others, but wanted to share that I tested this using SQL Server.

Edit: SCHEMA_NAME function isn't necessary. You can replace SCHEMA_NAME(schema_id) with the name of your schema in single quotes if you want, but either will work.

 SELECT t.name AS table_name,
      SCHEMA_NAME(schema_id) AS schema_name,
      c.name AS column_name
 FROM 
     sys.tables AS t
 INNER JOIN 
     sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
 WHERE 
      t.name = 'Table1' AND
      c.name Like 'Flag%'
 ORDER BY 
     c.name

or

 SELECT t.name AS table_name,
      'MySchema' AS schema_name,
      c.name AS column_name
 FROM 
     sys.tables AS t
 INNER JOIN 
     sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
 WHERE 
      t.name = 'Table1' AND
      c.name Like 'Flag%'
 ORDER BY 
     c.name