Correcting int identity values after a sql server 2005 to 2000 publish

You will need to run this sql:

DECLARE @TableName varchar(50), @ColumnName varchar(50)
DECLARE @SQLString nvarchar(500), @MaxID int

DECLARE curs CURSOR FOR 
--begin query
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME 
-- end query
 OPEN curs
 FETCH NEXT FROM curs INTO @TableName, @ColumnName
 WHILE (@@fetch_status <> -1)
 BEGIN
  IF (@@fetch_status <> -2 )
  BEGIN
   SET @SQLString = N'SELECT @MaxIDOUT = MAX( ' + @ColumnName + ') FROM ' + @TableName
   EXECUTE sp_executesql @SQLString, N'@MaxIDOUT int OUTPUT', @MaxIDOUT=@MaxID OUTPUT;
   DBCC CHECKIDENT(@TableName, 'RESEED', @MaxID)  
  END
 FETCH NEXT FROM curs INTO @TableName, @ColumnName
 END
DEALLOCATE curs

Finding your left and right neighboring rows using a temp table

/* declare variables */
DECLARE @userID INT
DECLARE @maxCnt INT
DECLARE @tmpUser TABLE (tmpId INT IDENTITY(0,1), UserID INT)

/* quick way to limit to one user */
SET @userID = 223

/* populate the temp table */
INSERT INTO @tmpUser (UserId)
   SELECT UserId FROM users

/* find max value for modulo math */
SELECT @maxCnt = COUNT(userid) FROM @tmpUser

--select * from @tmpUser

/* join against orginal data, using mod math and tmp table */

SELECT u.UserId, l.UserId, r.UserId 
  FROM users AS us
  left join @tmpUser AS u ON u.userid = us.userid 
  left join @tmpUser AS l ON ((u.tmpId - 1 + @maxCnt) % @maxCnt) = l.tmpId
  left join @tmpUser AS r ON ((u.tmpId + 1)% @maxCnt) = r.tmpId
  WHERE us.userID = @UserID

MS SQL Cursors Example

This is an example of how to drop a number of Tables in SQL server using a cursor:

DECLARE @name varchar(255)
DECLARE curs CURSOR FOR SELECT name
FROM sysobjects
WHERE name LIKE 'tmp_%'
AND DATEDIFF(day,crdate,getdate())>2
AND type = 'U'
OPEN curs
FETCH NEXT FROM curs INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2 )
BEGIN
PRINT 'DROPPING TABLE ' + @name
EXEC('DROP TABLE ' + @name )
END
FETCH NEXT FROM curs INTO @name
END
DEALLOCATE curs