Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Find all Views and SP that reference a column?

    I need to remove an existing pk field and then use another field.

    How can i write a sp that would find all tables & views & objects that have this field?

    I worked with someone that did this and had a select at the end, it was really cool !

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    You mean sp_depends?

    sp_depends (Transact-SQL)

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try this
    select object_name(id) from sys.syscomments where text like '%columnname%'

  4. #4
    Join Date
    Oct 2003
    Posts
    233
    I noticed that running both on Adventure works returns 2 diff result sets with some commen items and some items that are not included in each result.

    I read the doc on sp_depends, but dont know the functionality of:
    "select object_name(id) from sys.syscomments where text like '%columnname%'"

    can someone explain to me ?


    Thanks guys!

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    This is what I ended up with

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    /*
    mk_FindRelatedItems '%CustomerID%' ,'FIELD'
    go
    mk_FindRelatedItems 'Sales.Customer' ,'TABLE'
    */
    Create Proc [dbo].[mk_FindRelatedItems]
    @FindThis as varchar(50),
    @FieldTable as varchar(50)
    AS
    BEGIN
    SET NOCOUNT ON;

    if upper(@FieldTable) = 'FIELD'
    select object_name(id) as ObjectName
    from sys.syscomments
    where text like @FindThis


    SET @FindThis = replace(@FindThis,'%','')

    SELECT
    so.NAME AS TableName
    , so.id
    , so.crDate
    , so.refDate
    , sc.NAME AS ColumnName
    , sc.xtype
    , sc.xusertype
    , sc.length
    , sc.colorder
    FROM sysobjects so
    INNER JOIN syscolumns sc ON so.id = sc.id
    WHERE so.TYPE = 'U'
    and sc.name = ''+ @FindThis +''
    ORDER BY so.name, sc.colorder


    if NOT upper(@FieldTable) = 'FIELD'
    EXEC sp_depends @objname = @FindThis

    end

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    Quote Originally Posted by mikezx10
    I noticed that running both on Adventure works returns 2 diff result sets with some commen items and some items that are not included in each result.

    I read the doc on sp_depends, but dont know the functionality of:
    "select object_name(id) from sys.syscomments where text like '%columnname%'"

    can someone explain to me ?

    Thanks guys!
    select object_name(id) from sys.syscomments where text like '%columnname%'

    when u pass the columnname then u will get the sp& views names
    the sp's & views inthat given is used then it will show the list of the records

    exec sp_depends 'spname or view'
    then it will give u tables used in that database & columnnames

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Keep in mind that some of these techniques will miss instances were a column is referenced indirectly by "SELECT *", or the statement is formatted as dynamic SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •