Greg Robidoux has a very helpful post “SQL Server Find and Replace Values in All Tables and All Text Columns”, where he presents the following SQL:

SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT

SET @stringToFind = ‘Smith’
SET @stringToReplace = ‘Jones’

DECLARE TAB_CURSOR

CURSOR FOR SELECT B.NAME AS SCHEMANAME, A.NAME AS TABLENAME, A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = ‘U’
ORDER BY 1

OPEN TAB_CURSOR FETCH NEXT
FROM TAB_CURSOR INTO @schema, @table, @object_id WHILE @@FETCH_STATUS = 0

BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME FROM sys.columns A
INNER JOIN sys.types B ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id AND IS_COMPUTED = 0
AND B.NAME IN (‘char’,’nchar’,’nvarchar’,’varchar’,’text’,’ntext’)

OPEN COL_CURSOR FETCH NEXT
FROM COL_CURSOR INTO @columnName WHILE @@FETCH_STATUS = 0

BEGIN
SET @sqlCommand = ‘UPDATE ‘ + @schema + ‘.’ + @table + ‘ SET [' + @columnName + '] =
REPLACE(convert(nvarchar(max),[' + @columnName + ']),”’ + @stringToFind + ”’,”’ +
@stringToReplace + ”’)’ SET @where = ‘ WHERE [' + @columnName + '] LIKE ”%’ +
@stringToFind + ‘%”’

EXEC( @sqlCommand + @where)

SET @count = @@ROWCOUNT IF @count > 0

BEGIN
PRINT @sqlCommand + @where
PRINT ‘Updated: ‘ + CONVERT(VARCHAR(10),@count)
PRINT ‘—————————————————-’

END

FETCH NEXT FROM COL_CURSOR INTO @columnName END

CLOSE COL_CURSOR DEALLOCATE COL_CURSOR

FETCH NEXT FROM TAB_CURSOR INTO @schema, @table, @object_id END

CLOSE TAB_CURSOR DEALLOCATE TAB_CURSOR

Here is an SQL that you can run to find the total used size of an SQL Server 2008 database instance:

   CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),    data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))   exec sp_MSforeachdb '      use [+];       if (select DB_NAME())<> ''tempdb''          begin             EXEC sp_msforeachtable ''INSERT INTO #t EXEC sp_spaceused [?]''          end       ', '+'    SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) as sizeKB FROM #t     DROP TABLE #t  

Bill Graziano at SQLTeam.com has a listing of all the SQL Server versions that come in very handy:

http://www.sqlteam.com/article/sql-server-versions

This is an update to a previous post (Shrink All Log Files Within A MS SQL Instance) that has been edited to work in SQL Server 2008.

<br />
declare @ssql nvarchar(4000)<br />
set @ssql= '<br />
    if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')<br />
        begin<br />
            use [?]<br />
            declare @tsql nvarchar(4000) set @tsql = ''''<br />
            declare @recmodel nvarchar(10)<br />
            declare @iLogFile int<br />
            declare LogFiles cursor for<br />
            select fileid from sysfiles where  status &amp; 0x40 = 0x40<br />
            open LogFiles<br />
            fetch next from LogFiles into @iLogFile<br />
            while @@fetch_status = 0<br />
                begin<br />
                    select @recmodel = (SELECT recovery_model_desc FROM sys.databases WHERE name = ''?'' )<br />
                    if @recmodel = ''FULL''<br />
                        begin<br />
                            set @tsql = @tsql + ''use [?] ''<br />
                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY SIMPLE ''<br />
                            set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY FULL ''<br />
                        end<br />
                    else<br />
                        set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
                    fetch next from LogFiles into @iLogFile<br />
            end<br />
            --set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br />
            --print @tsql<br />
            exec(@tsql)<br />
            close LogFiles<br />
            DEALLOCATE LogFiles<br />
        end'<br />
exec sp_msforeachdb @ssql<br />

This little SQL script is used to to shrink all the log files in a Microsoft SQL server instance.  It uses the following function to get the job done: DBCC SHRINKFILE.

declare @ssql nvarchar(4000)<br />
set @ssql= '<br />
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin<br />
			use [?]<br />
			declare @tsql nvarchar(4000) set @tsql = ''''<br />
			declare @iLogFile int<br />
			declare LogFiles cursor for<br />
			select fileid from sysfiles where  status &amp; 0x40 = 0x40<br />
			open LogFiles<br />
			fetch next from LogFiles into @iLogFile<br />
			while @@fetch_status = 0<br />
			begin<br />
				set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
				fetch next from LogFiles into @iLogFile<br />
			end<br />
			set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br />
			--print @tsql<br />
			exec(@tsql)<br />
			close LogFiles<br />
			DEALLOCATE LogFiles<br />
        end'<br />
 exec sp_msforeachdb @ssql<br />
 

via CodeSnippets

Here is the t-sql statement used to shrink the SQL transaction logs:

Use
Go
Backup Log  With Truncate_Only
DBCC SHRINKFILE(, 1)
Go

I came across this script to backup all SQL Server databases:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:Backups'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor