Nov 08 2012

To query the schema (column and table names) of a sql database


USE dbname
GO

SELECT *
FROM information_schema.columns
WHERE column_name LIKE ‘%<partial column name…like email>%’

–To filter out the views (which you usually want)…
–this only works if prefix your views with rv or av.
–you could filter this by doing a join to information_schema.tables
–and doing where against table_type=’base table’
SELECT *
FROM information_schema.columns
WHERE column_name LIKE ‘%<partial column name…ie email>%’
AND column_name NOT LIKE ‘rv_%’
AND column_name NOT LIKE ‘av_%’

–to search for tables
SELECT *
FROM information_schema.tables
WHERE table_name LIKE ‘%<partial table name>%’

–in case you are wondering …
–when use with LIKE the % is called a wildcard.
–See if you can figure it out from that.