CODEWITHSUNDEEP
sql-serversql-server-2008
user1596078
user1596078

Reputation: 31

Find list of procedures using a specific column

If I provide the column name as input to the stored procedure should I get list of Procedures and table names using that column from all databases?

Upvotes: 2

Views: 728

Answers (1)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

for sql-server 2005+, you can use :

create procedure sp1
    @columnName varchar(30)
as
    select s1.name,'table' type
    from sys.objects s1 join sys.columns s2 on s1.object_id = s2.object_id
    where s2.name = @columnName  and s1.type = 'U'

    union

    select s1.name,'procedure' type
    from sys.procedures s1 join sys.sql_modules s2 on s1.object_id = s2.object_id
    and s2.definition like '%'+@columnName+'%'

Upvotes: 2

Related Questions