check_ident

Fix all identities on the SQL Server or just fix 'em in one database.

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.check_ident') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.check_ident
GO

CREATE PROCEDURE check_ident 
     @dbName VARCHAR(30) = ' ',
     @saPassword VARCHAR(20) = ' ',
     @debug VARCHAR(5) = 'false'       
AS
DECLARE @dbid INT,
 	@lastdbid INT, 
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(20)

SET NOCOUNT ON

SELECT @myName = 'check_ident'
SELECT @saPassword = ' '

IF @dbName = ' ' -- do em all
  BEGIN
    SELECT @dbid = 1
    SELECT @lastdbid = 5 -- skip the system dbs
  END
ELSE
  SELECT @dbid = DB_ID(@dbName)

WHILE @dbid IS NOT NULL
  BEGIN
    IF @dbName IS NULL -- do em all
      SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases
                      WHERE dbid > @lastdbid) 
 
    EXEC hold_semaphore 'textreader'

      TRUNCATE TABLE textreader
      SELECT count(*) FROM textreader WHERE 1=2
       
      INSERT textreader
      VALUES("USE " + DB_NAME(@dbid))

      INSERT textreader
      SELECT line 
      FROM scripts 
      WHERE name = 'fix_all_ident'
      ORDER BY ID

      SELECT @CMD = 'bcp admin..textreader out ' +
                    'C:\fix_' + DB_NAME(@dbid) + '_ident.sql -c -Usa -P' + 
                    @saPassword + ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus

      TRUNCATE TABLE textreader
 
    EXEC release_semaphore 'textreader' 

    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + 
                  ' -dmaster -i"C:\fix_' + DB_NAME(@dbid) + '_ident.sql"' +
                  ' -o C:\\fix_' + DB_NAME(@dbid) + '_ident.out -n'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    IF @dbName IS NULL
      SELECT @lastdbid = @dbid
    ELSE
      SELECT @dbid = NULL 
  END
GO