Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pass related tables / MSSQL

Posted on 2014-03-10
5
Medium Priority
?
302 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 668 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 668 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 664 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

721 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