Solved

pass related tables / MSSQL

Posted on 2014-03-10
5
251 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 167 total points
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Angel Strikes again
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2014 get SPIDs of users 6 25
Contained Database Collations 6 18
Auditing with Temporal Tables 4 16
Test a query 23 11
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now