Find Column Usage

  • kgayda

    SSCrazy

    Points: 2157

    Comments posted to this topic are about the item Find Column Usage


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • PCI

    SSC Journeyman

    Points: 76

    You have just a little problem with this.

    If the column name is not present in the SELECT section, but it's there in the WHERE clause,

    The stored procedure ignore this case.

    With this example:

    CREATE FUNCTION dbo.ufGetUserID (@p_Username varchar(20))

    RETURNS int as

    RETURN (

    SELECT usrID

    FROM TB_usrUser

    WHERE usrName = @p_Username

    )

    The function ufGetUserID is never returning,

    Because you search the column name before the table name !

  • kgayda

    SSCrazy

    Points: 2157

    I think you have mistaken this with another post.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • dhawalbatavia

    Valued Member

    Points: 60

    Thanks for the wonderful post, this is exactly what i was looking to implement. Good job.

  • kgayda

    SSCrazy

    Points: 2157

    Thank you.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • stephen.hendricks

    SSC Eights!

    Points: 845

    Does the script account for the possibility that the column name might cross a syscomments boundary (e.g., Part of the column name is at the end of syscomments.Row1 while the rest of the column name begins syscomments.row2) or do you feel that this condition won't occur?

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • kgayda

    SSCrazy

    Points: 2157

    I suppose that is a possibility but I have not encountered it yet.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • Mitch Rosenberg-300103

    Grasshopper

    Points: 10

    This may work better for the trigger references:

    SELECT DISTINCT SUBSTRING( SO.NAME, 1, 60 ) AS [Trigger Name]

    FROM sysobjects AS SO

    JOIN syscomments AS SC

    ON SO.ID = SC.ID

    WHERE SO.XTYPE = 'TR'

    AND SC.Text LIKE '%' + @vcColumnName + '%'

    AND ( @vcTableName = '' OR OBJECT_NAME( SO.Parent_Obj ) = @vcTableName )

    ORDER BY [Trigger Name]

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

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