Some time we get requirement to find a particulare sting across tables in a data base.
One of my friend was trapped in same situation this the first solution we found on net
Search string in all tables-Ist Solution
but not tried yet we'll continue work on this and let you know once we get something new.
Here is a way to search string in all tables in database this is one of the solution tried by my friend Rohit Rao and it is working solution all credit goes to him.
DECLARE @SearchStr NVARCHAR(100)
SET @SearchStr = 'F500P'
CREATE TABLE #results
(
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
DROP TABLE #results
Happy Living...
Happy Concepts...
Happy Programming...
