Post reply

Field Names

  • October 12, 2011 at 3:35 am

    #245189

    Hi everyone,

    I'm trying to find every table in my database that has a column called 'Companyid'. Is this easy to do?

    Many thanks for any help.

    Stuart.

  • October 12, 2011 at 3:38 am

    #1393321

    yes very easy

    select table_name from INFORMATION_SCHEMA.COLUMNS

    where COLUMN_NAME = 'Companyid'

  • October 12, 2011 at 4:00 am

    #1393326

    Thanks for you help.

    To take my problem one stage further, what I'm really trying to do is find every record in my database that has a companyid of 8514. How easy is that ?

  • October 12, 2011 at 4:07 am

    #1393327

    you could use the 'undocumented' command sp_foreachTable to loop through

    but in my opinion the 'easiest' way for me would be to use this code

    select 'select * from ' + table_name + 'where ' + column_name + '= 8514' from INFORMATION_SCHEMA.COLUMNS

    where COLUMN_NAME = 'Companyid'

    and cut + paste the results into a query window and execute, you could make it a bit more clever by creating and executing dynamic sql if you wish

  • October 12, 2011 at 4:07 am

    #1393328

    DECLARE @sql varchar(max)

    SET @sql = (

    SELECT 'SELECT ' + QUOTENAME(QUOTENAME(TABLE_SCHEMA) + + '.' + QUOTENAME(table_name),'''') +

    ', * FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(table_name) + ' WHERE CompanyId = 8514;'

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLUMN_NAME = 'CompanyId'

    FOR XML PATH('')

    )

    EXEC(@sql)

    -- Gianluca Sartori

  • October 12, 2011 at 8:30 am

    #1393487

    Thanks guys. That was a great help.

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

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