Contents

SQL Server: how to crawl all databases for data

Some weeks back I was challenged to figure out a way to easily, and without killing the server, track the location of some string data on all databases on a certain server. This is the result. (Btw, I’m not a DBA and this may not be the most optimal solution). Worked without any damage and gave the expected results.

In the last 3 years SQL Server has been around on my regular developer life but not with too much challenges. MySql, Sqlite3, etc is also also part of the fun but I tend to forget some stuff. This is reminder.

Problem

  • I want to search across dozens of database on a specific server;
  • I need to search for a string in a predefined and limited number of potential column names. Why limited? Because string search with LIKE is an expensive operation. In my scenario I was dealing with big databases (both in terms of data and number of table objects).

Plan

  • Get a list of databases with the possibility of excluding some system databases;
  • For each database get a list of all user tables;
  • Get the list of columns for each table, with the potential column name;
  • Apply a search of each found columns;
  • Output the database, table and column names for every match.

Sound simple…

Solution

I’ve used mainly cursors. If you are not very familiarized with it check the official documentation or this nice example.

I hope you may found this useful.

comments powered by Disqus