Post reply

Finding all columns in all tables with a given name

  • May 30, 2007 at 2:53 pm

    #174459

    Hi All

    I'm not too familiar with working with sys.objects etc.

    I have an application running on SQl Server 2005. It accesses 10 different databases, and each has an average of 40-50 tables (it's an ERP system).

    I need to write an update statement that updates all instances of a SiteID / SiteCode. All the columns that contain either a SiteID or SiteCode are named "SiteID", "SiteCode" or "Site_Code".

    What I want is a query that will search all tables in all DB's and list the following:

         Table Name

        Column Name

         DataType (e.g. varchar(2), int, etc)

    Where the column name is in ('SiteID', 'SiteCode', 'Site_Code') or like 'Site%'

    Can anyone help me out?

    Thanks

     

    mark

  • May 30, 2007 at 11:56 pm

    #709913

    Hi Mark,

    Perhaps something along the lines of this would do the trick (you should double check it!);

    select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,

    'SIZE/PRECISION'= CASE DATA_TYPE

    WHEN 'char' then CHARACTER_MAXIMUM_LENGTH

    WHEN 'varchar' then CHARACTER_MAXIMUM_LENGTH

    ELSE NUMERIC_PRECISION

    END,

    'SCALE'= CASE DATA_TYPE

    WHEN 'numeric' then NUMERIC_SCALE

    END

    from INFORMATION_SCHEMA.COLUMNS 

    where

    COLUMN_NAME in ('SiteID', 'SiteCode', 'Site_Code') or COLUMN_NAME like ''Site%'

    order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

    NB. Syntax will check all tables (User and System) but this should not be an issue given your selection criteria.  Alternately, join in 'INFORMATION_SCHEMA.TABLES' to limit the selection to 'BASE TABLE'.

    Finally, you could even create this as a UDF or SPR and use the SP_MSForeachDB to loop through each database.

    Hope that this helps,

    Duane

  • May 31, 2007 at 6:03 am

    #709965

    Hi Duane

     

    I tried it and it just returned tables from system. If I add "use <db name>" it does one db at a time. Anyway to do all db's together?

    Other than that: it work's great

     

    Thanks for the help

    Mark

  • June 6, 2007 at 10:02 am

    #711171

    Mark

    Either (a) create a temp table, insert the results for each database into it and then select them all, or (b) qualify the view name thus: MyDB.INFORMATION_SCHEMA.COLUMNS and use UNION to get the info for all databases into one result set.

    John

  • June 6, 2007 at 12:51 pm

    #711210

    Thnaks John

     

    I'll give it a try

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply