How to find all SQL tables with a column name

April 23, 2010 by Victoria Yudin 8 Comments


Often I need to find all the tables in Dynamics GP that have a particular column or search for all columns where I only have a partial column name. Over the years I have seen lots of different code to accomplish this, so this is nothing new. But I am asked about this enough that I thought I would share the code I use:

SELECT
    TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in
   (SELECT name
    FROM sysobjects
    WHERE xtype = 'U')
and COLUMN_NAME like '%xxxx%' -- replace with yours
ORDER BY COLUMN_NAME, TABLE_NAME

Just replace the xxxx with what you are looking for and run.

Updated on Jan 19, 2011 to add character maximum length.

GP Reports code, SQL coding, SQL Server
,

8 Responses to “How to find all SQL tables with a column name”

  1. David Rueter Reply October 27, 2015 at 3:56 pm

    Victoria,

    I always appreciate your excellent GP documentation. Thank you!

    FYI, here is a more modern version of your query that uses management views that are available on SQL2005 and later):

    SELECT
    sch.name AS SchemaName,
    tab.Name AS TableName,
    col.Name AS ColumnName
    FROM
    sys.tables tab
    JOIN sys.columns col ON
    tab.object_id = col.object_id
    JOIN sys.schemas sch ON
    tab.schema_id = sch.schema_id
    WHERE
    col.name LIKE ‘%BIN%’ –Replace BIN with your target string
    ORDER BY
    sch.Name,
    tab.Name,
    col.Name

    Like

  2. Baba Reply January 15, 2013 at 11:06 am

    Supb ts… great.. i tried 3 more queries frm other sites..unfortunately didn’t work out. But it does. Thanks a lot..

    Like

  3. Naseer Farooqi Reply May 7, 2010 at 12:28 pm

    Hi Victoria,

    I used this query to find out POP and PM link, which is not available on Microsoft site. So I used this query. Following is the modified script.

    SELECT
    TABLE_NAME, COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME in
    (SELECT name
    FROM sysobjects
    WHERE xtype = ‘U’)
    and COLUMN_NAME like ‘VCHRNMBR’ — replace with yours
    ORDER BY COLUMN_NAME, TABLE_NAME

    and my required result is POP30300.

    Thanks for your help.

    Like

  4. Waseem Reply April 25, 2010 at 8:52 am

    Hi Victoria,

    Very helpful post.

    Thanks

    Waseem.

    Like

  5. Naseer Farooqi Reply April 23, 2010 at 8:51 am

    Hi Victoria,

    Thanks for this new post. It is good and knowledable. But what is the meaning of xtype = U?

    Thanks

    Like

Leave a comment