EXEC sp_Msforeachtable 'IF COL_LENGTH(''?'',''Cust_ID'')>0 Update ?
Set CustID = newcustidvalue
Where CustID = oldvalue'

SET NOCOUNT ON

DECLARE	@WantedColumnName SYSNAME,
	@OldID VARCHAR(20),
	@NewID VARCHAR(20)

SELECT	@WantedColumnName = 'CustID',
	@OldID = 'ER00544',
	@NewID = 'PE1343'

DECLARE	@Exec TABLE
	(
		rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		SQL NVARCHAR(4000)
	)

INSERT		@Exec
		(
			SQL
		)
SELECT		N'UPDATE ' + QUOTENAME(c.TABLE_SCHEMA) + N'.' + QUOTENAME(c.TABLE_NAME)
		+ N' SET ' + QUOTENAME(@WantedColumnName) + N' = ' + QUOTENAME(@NewID, '''')
		+ N' WHERE ' + QUOTENAME(@WantedColumnName) + N' = ' + QUOTENAME(@OldID, '''')
FROM		INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN	INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
			AND t.TABLE_NAME = c.TABLE_NAME
WHERE		c.COLUMN_NAME = @WantedColumnName
		AND t.TABLE_TYPE = 'BASE TABLE'

DECLARE	@rowID INT,
	@SQL NVARCHAR(4000)

SELECT	@rowID = MAX(rowID)
FROM	@Exec

WHILE	@rowID > 1
	BEGIN
		SELECT	@SQL = SQL
		FROM	@Exec
		WHERE	rowID = @rowID

		RAISERROR('%s', 10, 1, @SQL) WITH NOWAIT
		EXEC sp_executesql @SQL

		SET	@rowID = @rowID - 1
	END

quote:

---

Originally posted by JimL

Works Great Peso(no Point in doing Views) Thanks Alot!!!!

Hey just Curious, How would we deal with if we wanted to use it across multiple DBs or all DBs as well?

Jim
Users <> Logic

---