mssql - search tables / procedures / views for keyword

[Costas]

search all tables field names

SELECT     c.name AS 'ColumnName'
           ,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM       sys.columns c
JOIN       sys.tables t  ON c.object_id = t.object_id
WHERE      c.name LIKE '%TOM%'
ORDER BY   TableName
           ,ColumnName;

search procs & views for a keyword

SELECT DISTINCT
   o.name AS ObjectName,
   o.type_desc AS ObjectType
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type IN ('P', 'V') -- P for procedures, V for views
   AND m.definition LIKE '%TOM%'
ORDER BY ObjectType, ObjectName;

search tables for a keyword

DECLARE @SearchKeyword NVARCHAR(100) = 'TOM'; -- Replace with your keyword
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @TableName NVARCHAR(256);
DECLARE @ColumnName NVARCHAR(256);

-- Cursor to iterate through all tables and columns
DECLARE db_cursor CURSOR FOR
SELECT
   TABLE_NAME,
   COLUMN_NAME
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext'); -- Adjust data types as needed

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = @SQL + 'SELECT ''' + @TableName + ''' AS TableName, ''' + @ColumnName + ''' AS ColumnName, ' + QUOTENAME(@ColumnName) + ' AS Value ' +
               'FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + @SearchKeyword + '%'' UNION ALL ';
  
   FETCH NEXT FROM db_cursor INTO @TableName, @ColumnName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Remove the last 'UNION ALL'
IF LEN(@SQL) > 0
BEGIN
   SET @SQL = LEFT(@SQL, LEN(@SQL) - 10); -- Remove the last 'UNION ALL'
  
   -- Print the generated SQL for debugging
   --PRINT @SQL;

   -- Execute the dynamic SQL
   EXEC sp_executesql @SQL;
END
ELSE
BEGIN
   PRINT 'No matching columns found.';
END