Friday, May 21, 2010

Quick Find Tables w/ Column Name Like

-- Utility: Quick Find Tables w/ Column Name Like
SELECT
'SELECT * FROM '
, t.TABLE_NAME
, 'WHERE Username LIKE ''kellyjo%'''
, ' -- '
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_CATALOG = 'foo'
AND c.COLUMN_NAME LIKE '%bar%'

No comments: