Dynamic SQL Query with MS SQL

Sometimes I wonder where a piece of information comes from in this intricate and giant software machine. I found myself in many circumstances in which I wanted to search the whole database, all tables, all rows for a value in order to know where it comes from.

For examples imagine you don't know much about the database and its 500 tables but you know it contains the value 1992281, representing some form of an ID.

The idea, then, is to:

1. Find tables with a column whose name is LIKE '%id%'
2. Find rows whose column value (converted to a string), is equal to, or LIKE (if you want to use wildcards), '1992281'

This is not for production, unless you don't have really good design (or extra-terrestrially complex software). That is because SQL queries should be much more efficient and simpler in production code which should be written by someone who understands the system.

DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @tempSQLQuery NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(MAX), @colName NVARCHAR(MAX);

column_name like ''%rel%'''

DECLARE relCols CURSOR FOR
                SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE table_schema = schema_name() AND column_name LIKE '%id%'

OPEN relCols;
FETCH NEXT FROM relCols INTO @tableName, @colName;
WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @tempSQLQuery = 'SELECT ''' + @tableName + ''' TableName, * FROM ' + @tableName + ' WHERE CAST(' + @colName + ' AS NVARCHAR) = ''1992281''' ;
   exec sp_executesql @tempSQLQuery;
  
      FETCH NEXT FROM relCols INTO @tableName, @colName;
   END;
CLOSE relCols;
DEALLOCATE relCols;
GO

So as you can see, this can be done by constructing a query based on what is contained in the INFORMATION_SCHEMA.COLUMNS table.

I just discovered that MS Sql Server Management Studio has a feature to 'Display Estimated Execution Plan'  (find it in the context menu in the SQL editor) that shows you a diagram of the steps that will be involved during execution of the SQL you typed. It also has detailed estimations such as average row size in bytes, CPU consumption, and numbers of executions!