How to identify the values against same field in all tables in database ?

  • How to identify the values against same field in all tables in database ?

    I have a field EmployeeID. This field is there in a good number of tables connected to parent table with a foreign key reference.

    I want to find out the value in this field in all tables in the database..

    Or

    I want to see if any data is there in this field in any of the tables.

    Please help

  • Hi Sanuj,

    First of all check for the tables which has field EmployeeID:

    SELECT O.Name TableName,C.Name ColumnName From sys.Objects O

    INNER JOIN sys.Columns C ON C.OBJECT_ID=O.OBJECT_ID

    WHERE C.NAME LIKE 'EmployeeID' AND type_desc='USER_TABLE'

    Now you can check the values in these tables.

  • thanks Hari

    I didn't post it (yet) but needed a way to find all fields called xxx

    in the database

    the system was designed by one programmer and he was highly disciplined in his naming convention

  • Thanks Hari.. Thank you very much 🙂

  • Hi Sanuj,

    here is how I handle this:

    -- =============================================

    -- Description:list all Fields of all tables of all databases with name like '%searchfor%%' auf

    -- =============================================

    Create PROCEDURE [dbo].[list_fields]

    @searchfor varchar(255)

    AS

    BEGIN

    SET NOCOUNT ON;

    declare @database varchar(100)

    declare @table varchar(100)

    declare @sql varchar(1000)

    -- temp. table

    CREATE table #tmp (

    datenbase_name varchar(100),

    table_name varchar(100),

    field_name varchar (100),

    fieldtype varchar (100),

    tabletype varchar (100)

    )

    -- loop for all databases

    declare cur cursor for

    select name from

    Master.sys.databases

    -- maybe you want to exclude some databases

    -- where name not in ('master', 'modell', 'msdb','tempdb','AdventureWorks', 'AdventureWorksDW', 'Berichtsserver', 'BerichtsserverTempDB', 'ProductCockpitReportServer', 'ProductCockpitReportServerTempDB')

    OPEN cur

    FETCH NEXT FROM cur

    INTO @database

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sql='insert into #tmp(datenbase_name, table_name, field_name, fieldtype, tabletype)'

    set @sql=@sql+' select C.TABLE_CATALOG, C.TABLE_NAME, COLUMN_NAME, DATA_TYPE, TABLE_TYPE from ' + @database + '.INFORMATION_SCHEMA.COLUMNS C'

    set @sql=@sql+' inner join ' + @database + '.INFORMATION_SCHEMA.TABLES T on C.TABLE_NAME=T.TABLE_NAME'

    set @sql=@sql+' where COLUMN_NAME like ''%' + @searchfor + '%'''

    execute (@sql)

    FETCH NEXT FROM cur

    INTO @database

    END

    CLOSE cur

    DEALLOCATE cur

    select * from #tmp order by datenbase_name, table_name, field_name

    DROP table #tmp

    /*

    exec list_fields 'EmployeeID'

    */

    END

    Regards

    Smerg

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

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