searching column name in databasebase objects

[fikir]

hello,

I am facing a big problem, I am changig the data type of one of my column from numeric to varchar(100), The conversion is not a problem, I did alter the column to varchar(100) in the table

what I want to find is all the names of database objects (procedures, fuctions, views..) where I am refering to this table, what I am now doing is a windows search, but this is a bit cumbersome because I have more than 100 database objects in my DB

Is there an easy way to get the name of this database objects

Thanks,

 

[gmmastros]

Use sp_depends. But be aware that it is not always 100% accurate.

sp_depends '[!]TableName[/!]'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fikir]

Thanks you george,

but this is returing all the db objects referencing the table though the column is not used there

can we do some kind of magic here and restrict the output to those DB objects refering the column

Thanks again

 

[PerlyGates]

This should get you started ...

DECLARE @myColumn varchar(50)

SET @myColumn = 'id' 

SELECT OBJECT_NAME(c.id)  AS ObjectName
      ,CASE type 
        WHEN 'P'  THEN 'Stored Proc'
        WHEN 'RF' THEN 'Replication Filter Stored Proc'
        WHEN 'X'  THEN 'Extended Stored Proc'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'V'  THEN 'View'
        WHEN 'FN' THEN 'Scalar Function'
        WHEN 'IF' THEN 'Inlined table function'
       END As ObjectType
  FROM syscomments c
INNER JOIN
       sysobjects o
   ON c.id         = o.id 
 WHERE text    LIKE  '%' + @myColumn + '%'
   AND(    TYPE = 'P'  
        OR TYPE = 'RF'  
        OR TYPE = 'X'   
        OR TYPE = 'TR'  
        OR TYPE = 'V' 
        OR TYPE = 'FN' 
        OR TYPE = 'IF')

 

[fikir]

brilliant

Thanks again