Solved

pass related tables / MSSQL

Posted on 2014-03-10
5
289 Views
Last Modified: 2014-03-13
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
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 167 total points
ID: 39917421
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
 
LVL 3

Author Comment

by:pma111
ID: 39917441
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 39917449
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
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 166 total points
ID: 39917456
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
 
LVL 22

Expert Comment

by:plusone3055
ID: 39917466
Angel Strikes again
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question