Parfois lancer un update sur l'intégralité d'une grosse table pose des problèmes de concurrence. Il peut être utile d'updater par lot.

SET STATISTICS IO OFF
SET ROWCOUNT 100000
 
DECLARE @cnt int
SET @cnt = 1
 
WHILE @cnt > 0 BEGIN
        UPDATE ...
 
        SET @cnt = @@ROWCOUNT
END

sp, fonctions… en utilisant syscomments

SELECT DISTINCT
o.xtype,
o.name
FROM
dbo.syscomments c
JOIN dbo.sysobjects o ON c.id = o.id
WHERE
text LIKE '%string_to_search%'
ORDER BY
o.xtype, o.name

SET STATISTICS IO OFF
SET NOCOUNT ON
 
EXEC sp_MSForEachdb
	@Command1 = 'USE ?; 
	DECLARE @clause varchar(40)
 
	SET @clause  = ''string_to_search''
 
	SELECT	''?'' as db, o.name, o.xtype --, *
	FROM	dbo.syscomments c
	JOIN	dbo.sysobjects o ON c.id = o.id
	WHERE	c.text LIKE ''%''+@clause+''%'' AND
		o.name NOT LIKE ''%''+@clause+''%''
	ORDER BY o.xtype, o.name'

SELECT i.name, i.rowcnt
FROM sysindexes i
JOIN sysobjects o ON i.id = o.id
WHERE o.name = 'table_name'
AND rowcnt > 0

SELECT 	'TRUNCATE TABLE dbo.['+name+']'
FROM 	dbo.sysobjects
WHERE 	xtype = 'U' AND
	name NOT IN ('dtproperties')
ORDER BY name

Plutôt que le ISNUMERIC() qui accepte les float, etc.

DECLARE @txt varchar(50)
SET @txt = '12349'
IF @txt LIKE REPLICATE('[0-9]', LEN(@txt))
	PRINT 'yes !'

CREATE TABLE #tmp (
	EventType varchar(100),
	Parameters int,
	EventInfo varchar(4000)
)
 
INSERT INTO #tmp EXEC ('DBCC INPUTBUFFER(@@SPID)')
 
SELECT * FROM #tmp

COLUMNS_UPDATED() n'est pas la fonction la plus facile à utiliser. C'est un euphémisme. Voici du code emprunté à Vlad Vissoultchev sur microsoft.public.sqlserver.programming. Attention, ce n'est valable qu'avec SQL Server 2000. Dans SQL Server 2005, on ne peut plus se fier à ORDINAL_POSITION. Voir l'aide en ligne de SQL 2005 pour la bonne façon de faire.

	SELECT COLUMN_NAME, ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME='temp'
	AND (SUBSTRING(COLUMNS_UPDATED(), (ORDINAL_POSITION-1) / 8 + 1, 1) & POWER(2, (ORDINAL_POSITION-1) % 8)) <> 0

Démonstration :

CREATE TABLE dbo.temp (id1 int, id2 int, id3 int, id4 int, id5 int, id6 int, id7 int, id8 int, id9 int)
GO
 
CREATE TRIGGER art$temp ON dbo.temp
AFTER UPDATE
AS BEGIN
	SELECT COLUMN_NAME, ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME='temp'
	AND (SUBSTRING(COLUMNS_UPDATED(), (ORDINAL_POSITION-1) / 8 + 1, 1) & POWER(2, (ORDINAL_POSITION-1) % 8)) <> 0 
END
GO
 
INSERT INTO dbo.temp VALUES (1,2,3,4,5,6,7,8,9)
GO
 
UPDATE dbo.temp SET id9 = 2
GO
UPDATE dbo.temp SET id3 = 2
GO
 
DROP TABLE dbo.temp
GO