{"id":313,"date":"2020-04-02T18:43:58","date_gmt":"2020-04-02T18:43:58","guid":{"rendered":"https:\/\/eipsoftware.com\/musings\/?p=313"},"modified":"2021-09-27T00:53:56","modified_gmt":"2021-09-27T00:53:56","slug":"column-names-by-table","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/column-names-by-table\/","title":{"rendered":"Column Names by Table"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><em>Where is it?  Seriously, where is it?<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We&#8217;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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"font:courier-new width-set:true width:800 lang:tsql range:1-100 decode:true \">SELECT\t\tinfocolumns.table_name\n\t\t\t,infoTables.table_type\n\t\t\t,infocolumns.ordinal_position \n\t\t\t,infocolumns.column_name \n\t\t\t,infocolumns.data_type\n\t\t\t,infocolumns.character_maximum_length\n\t\t\t,infocolumns.column_default \n\t\t\t,infocolumns.is_nullable\n\nFROM\t\tINFORMATION_SCHEMA.COLUMNS AS infocolumns\n\t\t\tINNER JOIN INFORMATION_SCHEMA.TABLES AS infoTables\n\t\t\tON infocolumns.TABLE_NAME = infoTables.TABLE_NAME\n\nWHERE\t\tinfocolumns.column_name LIKE '%YourFieldName%'\n\nORDER BY\tinfocolumns.table_name,\n\t\tinfocolumns.ordinal_position\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Edit the WHERE clause to either search by a column name or display all columns by replacing the WHERE clause as follows<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"width-set:true width:800 lang:tsql decode:true \">WHERE  infocolumns.table_name LIKE '%YourTableName%'<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Where is it? Seriously, where is it? We&#8217;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. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,6],"tags":[14,15,55],"series":[],"class_list":["post-313","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-sql","tag-sql","tag-t-sql","tag-utility"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=313"}],"version-history":[{"count":5,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":357,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/313\/revisions\/357"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=313"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}