Find Column Usage

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by kgayda » Find Column Usage

Find Column Usage

Rate Topic

Display Mode

Topic Options

Author

Message

kgayda

Posted Wednesday, October 03, 2007 5:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

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

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #406127

PCI

Posted Wednesday, October 10, 2007 6:37 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, November 02, 2011 9:50 AM
Points: 15, Visits: 30

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 !

Post #408955

kgayda

Posted Wednesday, October 10, 2007 1:16 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

I think you have mistaken this with another post.

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #409219

dhawalbatavia

Posted Friday, October 17, 2008 9:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 7, Visits: 136

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

Post #587741

kgayda

Posted Friday, October 17, 2008 1:30 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

Thank you.

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #587948

stephen.hendricks

Posted Wednesday, October 22, 2008 11:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, August 07, 2009 5:46 PM
Points: 295, Visits: 67

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)

Post #589990

kgayda

Posted Wednesday, October 22, 2008 1:34 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

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

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #590083

Mitch Rosenberg-300103

Posted Wednesday, December 01, 2010 10:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:15 AM
Points: 1, Visits: 117

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]

Post #1028799

« Prev Topic | Next Topic »

Permissions