Where In The World Is... Column 'Sandiego'?

OK, lame title...

So I'm making a change to a couple stored procs that is going to require change to a column length. Thing is, I can't remember where all I used that column...

This is actually the second time I'm posting this. The first one was ok, this one is much cleaner.

ALTER procedure [dbo].[proc_find_column]

-- Procedure to find all instances of a column
-- in all tables in a given database.  Supply the
-- database name and all or part of the column name.
-- Ex: exec dbo.proc_find_column 'databasename', 'columnname'
-- Or: exec dbo.proc_find_column 'databasename', '%name'

@dbname varchar(100), -- The name of the database to search
@colname varchar(100) -- The name of the column to locate
as

declare @sql nvarchar(4000)

set @sql = 'select table_name, column_name, data_type, character_maximum_length from '
set @sql = @sql + @dbname  
set @sql = @sql + '.INFORMATION_SCHEMA.COLUMNS where table_name in (select table_name from '
set @sql = @sql + @dbname
set @sql = @sql + '.INFORMATION_SCHEMA.TABLES where table_type = ''base table'' 
                    and table_name <> ''dtproperties'') and column_name like '''
set @sql = @sql + @colname
set @sql = @sql + ''' order by table_name, column_name'

exec master.dbo.sp_executesql @statement = @sql
--print @sql  --placed here for testing.

Now...

exec admin.dbo.proc_find_column 'auditdb', 'server_name' 

gives me ...

config_exceptions  server_name varchar  15
databases          server_name varchar  15
dba_errorlog_check server_name nvarchar 128
dboptions          server_name varchar  15
drivespace         server_name varchar  15
job_status         server_name varchar  15
jobs               server_name varchar  15
liveagents         server_name varchar  15
notifications      server_name varchar  15
server_config      server_name varchar  15
server_connections server_name varchar  15
sql_servers        server_name varchar  15

Much better... :-)

-D.