How to find a string from whole database?

Samirbhogayta
Posted by Samirbhogayta under Sql Server category on | Points: 40 | Views : 419
When you want to find any string or text from the whole database for creating custom search/site search at that time you can use this query. It's useful.

DECLARE @MyValue NVarChar(4000) = 'something';

SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
sys.tables T ON S.schema_id = T.schema_id;

WHILE (EXISTS (SELECT * FROM #T)) BEGIN
DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
DECLARE @TableName NVarChar(1000) = (
SELECT TOP 1 SchemaName + '.' + TableName FROM #T
);
SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);

DECLARE @Cols NVarChar(4000) = '';

SELECT
@Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
FROM sys.columns C
WHERE C.object_id = OBJECT_ID(@TableName);

SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
SELECT @SQL = @SQL + @Cols;

EXECUTE(@SQL);

DELETE FROM #T
WHERE SchemaName + '.' + TableName = @TableName;
END;

DROP TABLE #T;

Comments or Responses

Login to post response