Way back in 2015 I provided the SQL to search for a specific string in any field in any table in any database on a server. I don't remember why I needed that, but I guess I did. What I've come to use more frequently, however, is a search of every field in every table in a single database. So here's that query:
DECLARE @SearchText NVARCHAR(1000) = 'Some value' DECLARE @SchemaName NVARCHAR(256), @TableName NVARCHAR(256), @ColumnName NVARCHAR(256) DECLARE TableCursor CURSOR FOR SELECT sch.[Name] AS SchemaName, st.[Name] AS TableName, sc.[Name] AS ColumnName FROM sys.tables st WITH (NOLOCK) INNER JOIN sys.columns sc WITH (NOLOCK) ON st.object_id = sc.object_id INNER JOIN sys.schemas sch WITH (NOLOCK) ON st.schema_id = sch.schema_id OPEN TableCursor FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName, @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @InternalSQL NVARCHAR(MAX) = 'SELECT @CountParam = COUNT(*) FROM [' + @SchemaName + '].[' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @SearchText + '%''' DECLARE @Count INT EXEC SP_EXECUTESQL @InternalSQL, N'@CountParam INT OUT', @Count OUT IF (@Count > 0) BEGIN PRINT @SchemaName + '.' + @TableName + '.' + @ColumnName END FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName, @ColumnName END CLOSE TableCursor DEALLOCATE TableCursor
No comments:
Post a Comment