How to get the list of tables and their columns in a databse which are having a specific keyword in their name

Question

I have to make a list of tables and their columns that are having keyword 'EMP' in their column name. The database is having around 160 tables. Is there any way of finding this quick ?

Answer 1

Give this a go

SELECT  TABLE_NAME, 
        COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME LIKE '%YoutValue%'
ORDER BY    TABLE_NAME, 
            ORDINAL_POSITION

EDIT

You could use

    SELECT  TABLE_NAME, 
            COLUMN_NAME
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_NAME LIKE '%YoutValue%'
    OR    COLUMN_NAME LIKE '%YoutValue%'
    ORDER BY    TABLE_NAME, 
                ORDINAL_POSITION