pass related tables / MSSQL

is there any easy way in sql 2008 to run a query to list all databases in an instance which contain a field within a database containing either "pass" or "pwd"? And retunr the results as:

database, table, field

i.e.

database1,mytable.password
database3,histable.pwd
LVL 3
pma111Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have written this script to do this, as you can see, you can put a couple of values to search for, which can include % in the characters
use master 
go
create table query_results ( database_name sysname, table_owner sysname, table_name sysname, col_name sysname )
create table col_name_patterns ( pattern nvarchar(100) )
go
insert into col_name_patterns values ( 'pwd' )
insert into col_name_patterns values ( 'pass' )

--- here comes the running script, don't modify as from here:
set nocount on
declare @databases table ( database_name sysname )
declare @database sysname
declare @sql nvarchar(max)

insert into @databases ( database_name ) 
  select name from sys.sysdatabases
  where DATABASEPROPERTYEX(name, 'STATUS') = 'ONLINE'

  --select * from sys.sysdatabases

while @@ROWCOUNT > 0
begin
  select top 1 @database = database_name from @databases 
  if @@ROWCOUNT > 0
  begin
    set @sql = 'use [' + @database + ']
    insert into master.dbo.query_results ( database_name, table_owner, table_name, col_name )

	select table_catalog, table_schema, table_name, column_name
	  from information_schema.COLUMNS c
	  where exists(select null from master.dbo.col_name_patterns p
			where c.column_name like p.pattern )
	  '
	  exec(@sql)

  end -- processed 1 database
  delete @databases where database_name = @database
end -- looping on the databases list

select * from query_results
go
drop table query_results
go
drop table col_name_patterns 

Open in new window

0
 
plusone3055Commented:
here are a few articles that you should check out. as far as I've seen you cant search ALL databases on an instance for keywrods, however you can do it one at a time. Simply use the Stored procedure in the first link and cop/paste :)
 

'this one is a great Stored Procedure
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
 
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx



http://www.sqlusa.com/bestpractices2008/searchalltablesallcolumns/
0
 
pma111Author Commented:
New to MSSQL, how can you do wildcard type searches, i.e. contains "PASS" or "PWD", and will it be case sensitive?

EXEC SearchAllTables 'Computer'

Open in new window

0
 
AnujSQL Server DBACommented:
This will work.

EXEC sp_msforeachdb @command1 = 'SELECT ''?'' as DBName,TABLE_NAME, COLUMN_NAME
FROM ?.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME lIKE ''%PWD%'' OR COLUMN_NAME lIKE ''%pass%'''

Open in new window

0
 
plusone3055Commented:
Angel Strikes again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.