Dbspy proc: search all column name or table name in any db

SQL Server Administration
1

not sure if this might help anyone. feel free to enhance it, critic it. I run this type of query always as I deal with many databases.

use DBAdmin
go


alter proc dbspy
(
	@dbname varchar(50),
	@searchtype char(1),
	@searchvalue varchar(50)
)
as
begin

	declare @query nvarchar(max) 

	select @query = 'select t.name, c.name 
	                   from ' + @dbname + '.sys.tables t join ' + 
					   @dbname + '.sys.columns c on t.object_id = c.object_id ' ;
					   

	select @query = @query +
	               case @searchtype
				       when 'T' then 'where t.name like ''%' + @searchvalue + '%'''
					   else 'where c.name like ''%' + @searchvalue + '%'''
					end
	exec (@query)
end
go

exec dbspy 'mcsf', 'C' , 'DE.AE'
1 Like