/ Published in: SQL
Stored Procedure for search a string in all fields of all tables of one DataBase
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
CREATE Proc SEARCH ( @SearchStr nVarChar(100) ) AS BEGIN CREATE TABLE #Results ( TableName nVarChar(370), ColumnName nVarChar(370), ColumnValue nVarChar(3630)) SET NoCount ON DECLARE @TableName nVarChar(256), @ColumnName nVarChar(128), @SearchStr2 nVarChar(110), @ColumnValue nVarChar(4000) 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 + ''', ' + '''' + @TableName + '.' + @ColumnName + ''', ' + 'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT TableName, ColumnName, ColumnValue FROM #Results END