How to find all SQL tables with a column name


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.

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

  1. 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. Supb ts… great.. i tried 3 more queries frm other sites..unfortunately didn’t work out. But it does. Thanks a lot..

    Like

  3. 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. Hi Victoria,

    Very helpful post.

    Thanks

    Waseem.

    Like

  5. Hi Victoria,

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

    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: