How to find the location of cell value in SQL Server Database?

Question

How to find the location of cell value in SQL Server Database without knowing the column name or table name?

Vijay Pratap Singh · Answer

DECLARE @SearchStr nvarchar(100) = 'Stark'; 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' 'int' 'decimal') 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;

Sachin Singh · Answer

Test this DECLARE @SQL VARCHAR(MAX) DECLARE @SearchString VARCHAR(100) SET @SQL = '' -- Enter the string to be searched for here: -- SET @SearchString = 'sachin' SELECT @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX) COUNT(*)) + '' matches in column '' + ''' + C.name + ''' on table ''' + SC.name + '.' + T.name + ''' [Matches for ''' + @SearchString + ''':] FROM ' + QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) + ' LIKE ''%' + @SearchString + '%'' HAVING COUNT(*) > 0 UNION ALL ' + CHAR(13) + CHAR(10) FROM sys.columns C JOIN sys.tables T ON C.object_id = T.object_id JOIN sys.schemas SC ON SC.schema_id = T.schema_id JOIN sys.types ST ON C.user_type_id = ST.user_type_id JOIN sys.types SYST ON ST.system_type_id = SYST.user_type_id AND ST.system_type_id = SYST.system_type_id WHERE SYST.name IN ('varchar' 'nvarchar' 'text' 'ntext' 'char' 'nchar') ORDER BY T.name C.name -- Strip off the last UNION ALL IF LEN(@SQL) > 12 SELECT @SQL = LEFT(@SQL LEN(@SQL) - 12) EXEC(@SQL) -- PRINT @SQL

Nitin Sontakke · Answer

Not sure what you are looking for. Are you suggesting that you want to find out where in the database a specific value is not even knowing what the table or column name could be. In this case one thing that you could be sure of is at least the data type. If that also is not possible you would have to convert every data type to varchar. If you are okay with this then you will need something as follows: select 'select ''' + s.[name] + ''' [schema_name] ''' + o.[name] + ''' [table_name] ''' + c.[name] + ''' [column_name] convert(varchar(1000) [' + c.[name] + ']) [value] from [' + s.[name] + '].[' + o.[name] + '] where convert(varchar(1000) [' + c.[name] + ']) = '' '' union all ' from sys.columns c left outer join sys.objects o on c.[object_id] = o.[object_id] left outer join sys.schemas s on o.[schema_id] = s.[schema_id] where o.[type] = 'U' and o.[name] != 'dtproperties' IMPORTANT: This process is NOT going to be very good from performance point of view!

Deepak Tewatia · Answer

Thanks so much @Sachin and @Vijay