Background Story

You are assigned to a running project, with limited documentation. Live operations/transactions are going on in the project database. Now you need to understand the dataflow. Database table and column names are not much meaningful to you! Also, if the database has more than 50 stored procedures, it is difficult to find some hard code logic of those scripts which one that logic resides?

Possible Approach

If the project has any UI/Front End application, then from the project Front End/UI do some test entries and check which table/columns are storing those data.

Here, is one generic stored procedure to search with a keyword inside the entire database tables and stored procedures. It will give you the location of that keyword and which table/column or stored procedures it resides in.

CREATE PROCEDURE [dbo].[SearchAllTables] (
  @SearchStr nvarchar(100)
) AS BEGIN 

-- Purpose: To search all columns of all tables and stored procedures for a given search string
-- Written by: Md. Nazmul Hossain
-- Site: https://nhossaincse.com
-- Tested on: SQL Server 12.0

DECLARE @Results TABLE(
  ColumnName nvarchar(370), 
  ColumnValue nvarchar(3630)
) 
SET 
  NOCOUNT ON DECLARE @TableName nvarchar(256), 
  @ColumnName nvarchar(128), 
  @SearchStr2 nvarchar(110) 
SET 
  @TableName = '' 
SET 
  @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') 
WHILE @TableName IS NOT NULL 
BEGIN 
SET 
  @ColumnName = '' 
SET 
  @TableName = (
    SELECT 
      MIN(
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
      ) 
    FROM 
      INFORMATION_SCHEMA.TABLES 
    WHERE 
      TABLE_TYPE = 'BASE TABLE' 
      AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
      AND OBJECTPROPERTY(
        OBJECT_ID(
          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 
        'IsMSShipped'
      ) = 0
  ) 
WHILE (@TableName IS NOT NULL) 
  AND (@ColumnName IS NOT NULL) 
BEGIN 
SET 
  @ColumnName = (
    SELECT 
      MIN(
        QUOTENAME(COLUMN_NAME)
      ) 
    FROM 
      INFORMATION_SCHEMA.COLUMNS 
    WHERE 
      TABLE_SCHEMA = PARSENAME(@TableName, 2) 
      AND TABLE_NAME = PARSENAME(@TableName, 1) 
      AND DATA_TYPE IN (
        'char', 'varchar', 'nchar', 'nvarchar'
      ) 
      AND QUOTENAME(COLUMN_NAME) > @ColumnName
  ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  ) END END END 
  --SELECT ColumnName, ColumnValue FROM @Results
  --UNION 
  --DECLARE @Search varchar(255)
  --SET @Search='quizplay'
INSERT INTO @Results(ColumnName, ColumnValue) 
SELECT 
  DISTINCT o.name AS ColumnName, 
  o.type_desc as ColumnValue 
FROM 
  sys.sql_modules m 
  INNER JOIN sys.objects o ON m.object_id = o.object_id 
WHERE 
  m.definition Like '%' + @SearchStr + '%' --ORDER BY 2,1
SELECT 
  ColumnName, 
  ColumnValue 
FROM 
  @Results 
 END
Note: for a big database this take time and not supposed to run in production environment as may lead to lock of operations.  

Tagged in:

SQL