How to find a string inside a entire database?

Question

I have one specific string, such as "123abcd" for example but I don't know the name of the table or even the name of the column inside the table on my SQL Server Database. I want to find it with a select and show all columns of the related string, so I was wondering something like:

select * from Database.dbo.* where * like  '%123abcd%' 

For obvious reasons it doens't work, but there is a simple way to create a select statement to do something like this?

Answer 1

This will work:

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; 

A couple caveats, though. First, this is outrageously slow and non-optimized. All values are being converted to nvarchar simply so that they can be compared without error. You may run into problems with values like datetime not converting as expected and therefore not being matched when they should be (false negatives).

The WHERE (0 = 1) is there to make building the OR clause easier. If there are not matches you won't get any rows back.

Answer 2

Here are couple more free tools that can be used for this. Both work as SSMS addins.

ApexSQL Search – 100% free - searches both schema and data in tables. Has couple more useful options such as dependency tracking…

SSMS Tools pack – free for all versions except SQL 2012 – doesn’t look as advanced as previous one but has a lot of other cool features.