How to search for specific column name within one table

Question

I have searched this question on stackoverflow but most of the questions are a little deeper than what I want. Many questions are relating to finding the table that has the specific column name.

I am connected to the database through SSMS. I have found the table that I want to search through by SELECT * FROM Item. In the Item table I want to search all of the field names (or name of the column) and select the ones that contain a specific string 'Size'. I thought something like this would work

Select * FROM Item WHERE column_name LIKE '%SIZE%'

It doesn't work though. How do I specify it to search through all of the column names to find the names that contain 'Size'?

Thanks.

Do the answers from a similar, older question help? [Find all tables containing column with specified name - MS SQL Server](https://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name-ms-sql-server) — Milan, Jul 23 '18 at 19:31

score 1 · Answer 1 · answered Jul 23 '18 at 18:38

This should be the generic query to get you to what you want.

USE [database_name]
GO 
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 c.name LIKE '%SIZE%'
   AND t.name = 'Item'
ORDER BY schema_name, table_name;

You will need the correct permission on whichever SQL login that you run this through.

score 0 · Answer 2 · answered Jul 23 '18 at 18:41

0

SELECT [Name] 
FROM sys.columns
WHERE OBJECT_NAME(object_id)='Item'
AND [Name] LIKE '%Size%';

answered Jul 23 '18 at 18:41

Cetin Basoz

22,495
3
31
39

score 0 · Answer 3 · answered Jul 23 '18 at 18:42

0

You can use

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='zzzzz' and COLUMN_NAME like '%size%'

answered Jul 23 '18 at 18:42

tallwithknees

151
1
7

score 0 · Answer 4 · answered Jul 23 '18 at 18:55

0

This looks through table named Item for a Column with the name like 'SIZE':

SELECT sch.COLUMN_NAME, sch.* 
FROM INFORMATION_SCHEMA.COLUMNS AS sch
WHERE TABLE_NAME = 'Item'  
  AND COLUMN_NAME LIKE '%SIZE%'

Is this what you wanted?

answered Jul 23 '18 at 18:55

Milan

294
2
10

score 0 · Answer 5 · answered Jul 23 '18 at 18:59

I think this is what you are looking for, just replace database_Name with your db name:

Declare @myQuery varchar(max) = ' Select ';
Declare @columnName varchar(max) = '';

Declare GetColumnNames Cursor
For
    SELECT COLUMN_NAME
    FROM database_Name.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = N'Item'
        And database_Name.INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME like '%SIZE%'
OPEN GetColumnNames  

FETCH NEXT FROM GetColumnNames   
INTO @columnName

WHILE @@FETCH_STATUS = 0  
BEGIN
    Set @myQuery += @columnName + ', '

    FETCH NEXT FROM GetColumnNames   
    INTO @columnName
END   
CLOSE GetColumnNames;  
DEALLOCATE GetColumnNames;

-- Chop off the end character
SET @myQuery = LEFT(@myQuery, LEN(@myQuery) - 1) 

Set @myQuery += ' From Item'
exec(@myQuery)

score 0 · Answer 6 · answered Jul 23 '18 at 20:32

You'll have to take a two step approach to achieve your end query.

First, you'll need to identify the columns you're interested in by using the table metadata, which you can get from either the sys schema or the INFORMATION_SCHEMA tables. Several of the proposed answers will help you get that information.

Next, you'll use the column names you've identified in step one to build the actual query you're interested in. If this is a one-off task you're doing, just copy and paste the results from the meta data query into a new SELECT query as your column list. If you need to do this task programmatically or multiple times using different LIKE strings, then you'll want to invest the time in writing some dynamic SQL.

When you wrap it all up, it'll look something like this:

--Step 1; The meta data part

DECLARE @ColumnList NVARCHAR(MAX)
       ,@SQL NVARCHAR(MAX)

SELECT 
  @ColumnList = COALESCE(@ColumnList+',','') + COLUMN_NAME
FROM 
  INFORMATION_SCHEMA.COLUMNS
WHERE 
  TABLE_SCHEMA = 'schema'
  AND 
  TABLE_NAME = 'TableName'
  AND 
  COLUMN_NAME LIKE '%SIZE%'

SELECT @ColumnList;

--Step 2; The dynamic SQL part

SET @SQL = 'SELECT ' + @ColumnList + ' FROM schema.TableName;';

EXECUTE sys.sp_executesql @SQL;

How to search for specific column name within one table

6 Answers6