Find Column Usage

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


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • 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 !

  • I think you have mistaken this with another post.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

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

  • Thank you.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • 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)

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


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • This may work better for the trigger references:

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

    FROM sysobjectsAS SO

    JOIN syscommentsAS 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 7 (of 7 total)

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