TSQL script to loop round all databases

Hi Guys

Quick script to loop round all databases and perform an action - in this case its setting all databases to simple recovery. The keyword here is sp_msforeachdb - pure genius :)

use master
GO
sp_msforeachdb '
declare @sql VARCHAR(1000)
IF DATABASEPROPERTYEX(''?'', ''Rjavascript:void(0)ecovery'') <> ''SIMPLE''
begin
set @sql = ''ALTER DATABASE ? SET RECOVERY SIMPLE''
PRINT @sql

end'

Format uppercase strings in SQL

Hi,

A useful SQL function here. It was written for SQL Server 2000, but I'm sure it can be adapted for any other SQL technologies.

It takes a string and changes it to be uppercase for the first char of every word and lower for the rest of it. E.g. "SQL IS GREAT FUN" becomes "Sql Is Great Fun".

Not much else to say... here it is!

CREATE FUNCTION dbo.ReturnFormattedString(@IN_String varchar(1000))
RETURNS varchar(1000) AS
BEGIN

DECLARE
@NewValue VarChar(1000)
, @X int
, @NewWord bit


SET @x = 1

SET @NewWord = 1

SET @NewValue = ''
WHILE (@x <= Len(@IN_String))
BEGIN

If (@NewWord = 1) and (
(ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
OR (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
)
begin

if (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
SET @NewValue = @NewValue + Char(ASCII(SUBSTRING(@IN_String, @x, 1))-32)
else
SET @NewValue = @NewValue + SUBSTRING(@IN_String, @x, 1)

SET @NewWord = 0
end
else
begin
if (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
SET @NewValue = @NewValue + Char(ASCII(SUBSTRING(@IN_String, @x, 1))+32)
else
SET @NewValue = @NewValue + SUBSTRING(@IN_String, @x, 1)
end



SET @x = @x + 1


if Not
(
(ASCII(SUBSTRING(@IN_String, @x, 1)) >= 97) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 122)
OR (ASCII(SUBSTRING(@IN_String, @x, 1)) >= 65) and (ASCII(SUBSTRING(@IN_String, @x, 1)) <= 90)
)
SET @NewWord = 1
END

RETURN(@NewValue)

END