Find a string by searching all tables in SQL Server Management Studio 2008
Q) Is there any way to search for a string in all tables of a database in SQL Server Management Studio 2008? I want to search for string say john . The result should show the tables and their respective row that contain john . Solution: USE DATABASE_NAME DECLARE @ SearchStr nvarchar ( 100 ) = 'SEARCH_TEXT' 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' ...