Finding all occurenses of a field in a databaes

  • HI. Can someone tell me of an easy way to locate all the occurences of a particular field name in all tables of a particular database? For example: I'm looking for the field named 'first_name'. I want to know all tables that this field resides in a particular database.

    Thanks,

    Juanita

     

  • Try:

    SELECT *

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLUMN_NAME = 'first_name'

    or with wildcards

    SELECT *

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLUMN_NAME LIKE '%first%'

    will find every occurence of the word "first" in the database if you are set to a case insensitive language.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • THANK YOU SO MUCH !!

    That is exactly what I needed !!

    Juanita 

     

     

  • Just take all of the joy out of my life!

    And here I've been developing this query

    CREATE view dbo.vzTableColumnNames as

    select o.name as TableName, c.name as ColumnName, c.ColID,

     t.name + '(' + rtrim(cast(c.Length as char(3))) +

     case when c.xscale > 0 then

    --  '[' + rtrim(cast(c.xprec as char(2))) +  --xprec = c.length for numeric data types

      ',' + rtrim(cast(c.xscale as char(2))) + ')' else ')' end as ColType,

     case c.Isnullable when 1 then 'null' else 'not null' end as IsNullable,

     case when d.text is null then '' else ('DEFAULT ' + d.text) end as iDefault

     , FieldCount

    from syscolumns c   --field names

    inner join sysobjects o   --table names

     on c.id = o.id

    inner join systypes t   --data type names

     on c.xtype = t.xtype

    left join syscomments d   --defaults

     on c.cdefault = d.id

    inner join (select [id], count(*) FieldCount

     from syscolumns group by [id]) fc

     on o.id = fc.id

    where o.xtype = 'U'   --user tables

    --order by o.name, c.colid

    to provide largely the same information, and you just reduce it to a single statement.  Swine. 🙂

    I've always enjoyed querying the base tables, and I've never taken the time to play with the schema views, so thanks, Jim, for your reply.  I'm looking forward to studying it further.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • What I've actually have done on some of my servers is edit the model to add the put the view

    Create View IS.COLS

    as

    SELECT *

    FROM INFORMATION_SCHEMA.COLUMNS

    So I can get to it even quicker. I haven't ever used anything but the supplied tools that come with SQL Server. I haven't found I really need them. Well, really, my boss doesn't want to cough up the extra $$$ so I have to do without.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

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

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