This is an improvement on the previous PII finder scripts. Since often times production db servers have many databases, this will iterate over ALL DATABASES at once.
IMPORTANT: Run select name, database_id from sys.Databases to figure out which databases you are interested in querying, and adjust the counter accordingly. Most times, there's a handful of system databases that are unlikely to be useful for finding PII, so I have excluded the first four by default:
declare @dbname VARCHAR(60) declare @counter int;
declare @maxnames int;
declare @piisearch VARCHAR(500);
set @maxnames = (select count(name) from sys.databases where database_id > 4); /*exclude system tables, will vary based on database*/
set @counter = 4; /*change it here too*/
while @counter < @maxnames begin
set @dbname = (select name from sys.databases where database_id = @counter)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%SSN%'';'
execute(@piisearch)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%pw%'';'
execute(@piisearch)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%asswor%'';'
execute(@piisearch)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%sername%'';'
execute(@piisearch)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%DOB%'';'
execute(@piisearch)
SET @piisearch = 'use '+ @dbname + ' SELECT '''+@dbname+''' as DatabaseName, c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ''%license%'';'
execute(@piisearch)
set @counter = @counter + 1
/*print @dbname*/
End
