Where is it? Seriously, where is it?
We’ve all had the experience of trying to locate a specific column name or wanting to see the meta-data for columns in a specific or multiple tables. By reading the information schema tables we can see the meta-data and find all occurences of the column in the database. This information can be quite useful when needing to join columns and finding some hidden relationships.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT infocolumns.table_name ,infoTables.table_type ,infocolumns.ordinal_position ,infocolumns.column_name ,infocolumns.data_type ,infocolumns.character_maximum_length ,infocolumns.column_default ,infocolumns.is_nullable FROM INFORMATION_SCHEMA.COLUMNS AS infocolumns INNER JOIN INFORMATION_SCHEMA.TABLES AS infoTables ON infocolumns.TABLE_NAME = infoTables.TABLE_NAME WHERE infocolumns.column_name LIKE '%YourFieldName%' ORDER BY infocolumns.table_name, infocolumns.ordinal_position ; |
Edit the WHERE clause to either search by a column name or display all columns by replacing the WHERE clause as follows
|
1 |
WHERE infocolumns.table_name LIKE '%YourTableName%' |
Leave a Reply