SQL Loop

declare @query varchar(100),@dbname sysnamedeclare @dblist table (dbname sysname)insert into @dblist(dbname) select name from sys.databaseswhere name not in ('master', 'model', 'msdb', 'tempdb')while (select count(*) from @dblist) > 0beginselect top 1 @dbname = dbname from @dblistselect @query = 'dbcc checkdb(' + quotename(@dbname) + ')'exec(@query)delete from @dblist where dbname = @dbnameend
go

Search all fields in a table

CREATE PROCEDURE [dbo].[FindValue] @TableName NVARCHAR(128), /* Must be a valid table or view name, must not be quoted or contain a schema*/ @Value NVARCHAR(4000), /*May contain wildcards*/ @schema NVARCHAR(128) = 'dbo' /*May be left out*/ AS /* Sample Execution Exec FindValue @TableName = 'spt_monitor', @Value = '8', @schema = 'dbo' */ /* If given a string it will finds all rows where any char, varchar, or their Unicode equivalent which contain that string in the selected table or view. Note that this only works on objects which have entries in information_schema.columns, which excludes certain system objects. If given a numeric value it will check those text types for a match as well as numeric types. If given a possible date, it will also check date type. The string that is being searched for may contain wildcard characters such as %. This will NOT search text, ntext, xml, or user defined fields. This may return a row more than once if the search string is found in more than one column in that row. */ /**************************** Declare Variables ***********************/ DECLARE @columns TABLE (ColumnName NVARCHAR(128)) DECLARE @sql NVARCHAR(MAX) /************************** Populate Table Variable *****************/ /*Takes the names of string type columns for the selected table */ INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('char', 'nchar', 'varchar', 'nvarchar') /* If it is numeric, also check the numeric fields */ IF ISNUMERIC(@value) = 1 INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('int', 'numeric', 'bigint', 'money', 'smallint', 'smallmoney', 'tinyint', 'float', 'decimal', 'real') IF ISDATE(@value) = 1 INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('datetime', 'smalldatetime') /********************* Prepare dynamic SQL Statement to Execute **********/ SELECT @sql = CASE WHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' ' WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' ' END FROM @columns /******************* Execute Statement and display results ***********/ --print @sql /* This may be uncommented for testing purposes */ EXEC (@sql)