Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

pass related tables / MSSQL

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

877 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