Searching Unidentified Tables and Columns for Specific Content

earching for specific content or a specific column is a common task for a SQL Server administrator or developer. If you're familiar with the database or it's documented, searches aren't a problem. However, you're not always that lucky. Sometimes the database you need to search is unfamiliar or undocumented, which makes finding specific content or a specific column a challenge. This article is here to help. It presents a stored procedure that searches columns for content and a SELECT statement solution that matches a pattern or keyword in column names.

A quick Internet search will turn up some decent but limited solutions for searching unidentified tables and columns for specific content. Some don't consider all character data types, while others promote the unwise approach of using cursors. You should avoid cursors because of their potential for memory leaks if you don't close them properly. In many cases, you can handle a runaway cursor by searching for and killing the SPID (the connection ID), but runaway cursors are unpredictable. You might need to restart the SQL Server service to restore resources, which to your users might as well be the whole server.