SQL Server Find and Replace Values in All Tables and All Text Columns

ref: http://www.mssqltips.com/tip.asp?tip=1555


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

To search all columns of all tables in a database for a keyword

Makes life easy by search for a column

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (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'
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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

ref: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Understanding View State in ASP.NET

good article:

http://aspalliance.com/909_Understanding_View_State_in_ASPNET.all

Dynamic SQL use sp_executesql instead of EXECUTE Command

sp_executesql is more efficient, faster in execution and also supports parameter substitution

refer
http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx

Javascript Postcode formatting

function FormatPostcode()
{
var sPostcode;var objTempField;objTempField = document.getElementById('txtPostcode');
sPostcode = objTempField.value;sPostcode = sPostcode.toUpperCase().replace(' ','');

if (sPostcode.length >= 5){sPostcode=sPostcode.substr(0,sPostcode.length-3) + ' ' + sPostcode.substr(sPostcode.length-3,3);
}
objTempField.value=sPostcode;
}

file

PHP first letter of each word is capitalized

from HERE

function formatArray(s:String):String { // rraWrd is array to hold split values from passed string parameter. var rraWrd:Array = s.split(" "); // stgNew is a string into which to concatenate the new full sentence. var stgNew:String = ""; var nbrLen = rraWrd.length; for (var i:Number = 0; i < nbrLen; i++) { rraWrd[i] = rraWrd[i].substring(0, 1).toUpperCase() + rraWrd[i].substring(1); stgNew += (i < nbrLen - 1) ? rraWrd[i] + " " : rraWrd[i]; } return stgNew;}//var stgSentence:String = "This is not a love song";trace("Before: " + stgSentence);stgSentence = formatArray(stgSentence);trace("After: " + stgSentence);