top of page

Experienced Technology Product Manager adept at steering success throughout the entire product lifecycle, from conceptualization to market delivery. Proficient in market analysis, strategic planning, and effective team leadership, utilizing data-driven approaches for ongoing enhancements.

  • Twitter
  • LinkedIn
White Background

Stored Procedure to search all available tables inside MS SQL

Create Stored Procedure


CREATE PROC SearchAllTables
@SearchStr nvarchar(100)
AS
BEGIN
DECLARE @dml nvarchar(max) = N''      
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
CREATE TABLE dbo.#Results
([tablename] nvarchar(100),
 [ColumnName] nvarchar(100),
 [Value] nvarchar(max))
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' +
               c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) +
              ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
              ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
                  JOIN sys.columns c ON t.object_id = c.object_id
                  JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')
INSERT dbo.#Results
EXEC sp_executesql @dml
SELECT *
FROM dbo.#Results
END

Execute Stored Procedure

exec SearchAllTables '<<VALUE-ONE-WOULD-LIKE-TO-SEARCH';

Delete Stored Procedure

DROP PROCEDURE dbo.SearchAllTables

250 views0 comments

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page