How to use like operator to get multiple columns?

• subramanyammaruthi

  SSC Veteran

  Points: 275

  More actions

  September 12, 2015 at 4:20 am

  #307616

  Hello!!

  Can any one please let know how to use like operator select statement to retrieve multiple column names in sql server DB

  for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%'

  And what is this below query used for?

  SELECT column_name as 'Column Name', data_type as 'Data Type',

  character_maximum_length as 'Max Length'

  FROM information_schema.columns

  WHERE table_name = 'tblUsers'

• Kristen-173977

  SSCrazy Eights

  Points: 8687

  More actions

  September 12, 2015 at 4:29 am

  #1826204

  subramanyammaruthi (9/12/2015)

  ---

  And what is this below query used for?

  SELECT column_name as 'Column Name', data_type as 'Data Type',

  character_maximum_length as 'Max Length'

  FROM information_schema.columns

  WHERE table_name = 'tblUsers'

  Try running it ... if 'tblUsers' is not a table in your database replace that with the name of an existing table

  Some specific permissions are needed to access some of the information_schema views

• Jason A. Long

  SSC-Insane

  Points: 23789

  More actions

  September 12, 2015 at 1:09 pm

  #1826215

  subramanyammaruthi (9/12/2015)

  ---

  Hello!!Can any one please let know how to use like operator select statement to retrieve multiple column names in sql server DBfor ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?SELECT column_name as 'Column Name', data_type as 'Data Type',character_maximum_length as 'Max Length'FROM information_schema.columns WHERE table_name = 'tblUsers'

  The query simply returns the column names and their data types from a table called tblUsers.For the 1st question... how to find all columns in the employees table that have names that begin with "emp_" or "acc_", the query looks like this...

  SELECT    
    c.TABLE_CATALOG,    
    c.TABLE_SCHEMA,    
    c.TABLE_NAME,    
    c.COLUMN_NAME,    
    c.ORDINAL_POSITION,    
    c.COLUMN_DEFAULT,    
    c.IS_NULLABLE,    
    c.DATA_TYPE,    
    c.CHARACTER_MAXIMUM_LENGTH,    
    c.CHARACTER_OCTET_LENGTH,    
    c.NUMERIC_PRECISION,    
    c.NUMERIC_PRECISION_RADIX,    
    c.NUMERIC_SCALE,    
    c.DATETIME_PRECISION,    
    c.CHARACTER_SET_CATALOG,    
    c.CHARACTER_SET_SCHEMA,    
    c.CHARACTER_SET_NAME,    
    c.COLLATION_CATALOG,    
    c.COLLATION_SCHEMA,    
    c.COLLATION_NAME,    
    c.DOMAIN_CATALOG,    
    c.DOMAIN_SCHEMA,    
    c.DOMAIN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS c
WHERE
    c.TABLE_NAME = 'employees'    
    AND (
        c.COLUMN_NAME LIKE 'emp[_]%'    -- note that underscores a single character wildcards    
        OR
        c.COLUMN_NAME LIKE 'acc[_]%'    -- to include them as litterals in the search, you must "escape" then using sqiare brackes
        );