Here’s some quick sql logic to perform a wild card search across all tables and columns. In this example I’m looking for any tables or views that contain a column with the name of ‘user’. Why am I doing this? So that I can drill down to see what kind of user information is stored in the application. This is essential when doing migrations so you can ensure that all the appropriate users are migrated. Additionally; it’s a great way to perform application auditing for the apps teams.
use mydb; set nocount on select ist.table_type , isc.table_name , isc.column_name from information_schema.columns isc join information_schema.tables ist on isc.table_name = ist.table_name where column_name like '%user%' order by table_name , column_name asc
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Big Data, Command Prompt, Database Mail, Database Mirroring, ELI5, Error Solutions, Featured, Folder System, Forensics & Auditing, High Availability, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds