Post reply

Finding where a column name has been used

  • April 19, 2007 at 3:55 am

    #123549

    Hello,

    I need to find out where a certain column name has been used in the database.

    Example  : I need to find where column name ItemCode has been used in database Masterfoods

    I need to return the tablename, and the type of the column in that table.

    Any idea how I can do this?

    Thanks

  • April 19, 2007 at 4:06 am

    #701365

    You want to count ALL instances?

    Views, dynamic SQL, stored procedures and so on?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • April 19, 2007 at 4:21 am

    #701368

    I need to look at tables, stored procedures (declarations) and views

    There no need to look at dynamic SQL because it is not being used....

    Thanks

  • April 19, 2007 at 5:45 am

    #701393

    For tables and view you can use this query:

    select o.name, xtype

    from sysobjects o

    WHERE id in (Select id from syscolumns where name ='column_name')

    For stored procedures and functions you can then run sp_depends for each of the tables.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • April 19, 2007 at 10:27 am

    #701498

    Here's a way to search stored procedures, views, and UDFs:

    declare    @Target varchar(100)  

    set @target = '%searchstring%'          -- replace with string to search on

        

      

    SELECT  

            so.Name,  

            sc.Text,  

            CASE  

            WHEN xtype = 'P' then 'Stored Procedure'  

            WHEN xtype = 'V' then 'View'  

            WHEN xtype = 'IF' then 'Function'  

            ELSE xtype  

            END  

            AS 'Object Type'   

    FROM  

            sysobjects so JOIN syscomments sc ON sc.id = so.id 

            left outer join  syscomments sc1 on  sc1.id = sc.id and  sc1.colid = sc.colid + 1 

    WHERE   

       ( sc.text like @target

         or right(sc.text,500) + left(isnull(sc1.text,''),500) like @target

       )

        ORDER BY [Object Type], Name  

    Greg

    Greg

  • April 20, 2007 at 3:23 am

    #701655

    Possibly easier to remember for column names in tables is to use the INFORMATION_SCHEMA views SQL sets up for ADO i.e.:

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%yourname%'

    Adjust SELECT * for just the bits you need

    I have used Like incase you have ge. main_customerid and last_customerid and customerid etc ...

    James Horsley
    Workflow Consulting Limited

  • April 21, 2007 at 1:43 am

    #701830

    Thank You Guys for the examples

  • April 23, 2007 at 5:29 pm

    #702050

    If you just need to do this one time you can script the entire database to a file. This will create a text file that you can search.

    I do this often when I want to delete table columns to make sure they are not being used in views or sprocs.

  • April 24, 2007 at 5:50 am

    #702121

    The other option is to open Query Analyzer for SQL 2000 and press F4.

    You will get a search utility that can search for columns.

Viewing 9 posts - 1 through 8 (of 8 total)

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