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

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 ?

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