https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity استفاده کردم.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_FixSeeds2012] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_FixSeeds2012]
AS
BEGIN
--foreach database
DECLARE @DatabaseName varchar(255)
DECLARE DatabasesCursor CURSOR READ_ONLY
FOR
SELECT name
FROM sys.databases
where name not in ('master','tempdb','model','msdb') and sys.databases.state_desc = 'online'
OPEN DatabasesCursor
FETCH NEXT FROM DatabasesCursor
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('USE ['+@DatabaseName + ']
--foreach identity column
DECLARE @tableName varchar(255)
DECLARE @columnName varchar(255)
DECLARE @schemaName varchar(255)
DECLARE IdentityColumnCursor CURSOR READ_ONLY
FOR
SELECT
TABLE_NAME =t.name,
COLUMN_NAME =c.name,
TABLE_SCHEMA = s.name
FROM
sys.schemas AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.identity_columns AS ic on c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY
s.name,
t.name,
c.name
SELECT
TABLE_NAME =t.name,
COLUMN_NAME =c.name,
TABLE_SCHEMA = s.name
FROM
sys.schemas AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.identity_columns AS ic on c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE last_value IS NOT NULL
GROUP BY
s.name,
t.name,
c.name
OPEN IdentityColumnCursor
FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName
WHILE @@FETCH_STATUS = 0
BEGIN
print ''['+@DatabaseName+'].[''+@tableName+''].[''+@schemaName+''].[''+@columnName+'']''
EXEC (''declare @MAX int = 0
select @MAX = max(''+@columnName+'') from ['+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
if (@MAX IS NULL)
BEGIN
SET @MAX = 0
END
IF ('''''+@DatabaseName+''''' LIKE ''''%.%'''')
BEGIN
DBCC CHECKIDENT([''+@schemaName+''.''+@tableName+''],RESEED,@MAX)
END
ELSE
BEGIN
DBCC CHECKIDENT(['+@DatabaseName+'.''+@schemaName+''.''+@tableName+''],RESEED,@MAX)
END
'')
FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName
END
CLOSE IdentityColumnCursor
DEALLOCATE IdentityColumnCursor')
FETCH NEXT FROM DatabasesCursor
INTO @DatabaseName
END
CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor
END
EXEC sp_procoption @ProcName = 'sp_FixSeeds2012'
, @OptionName = 'startup'
, @OptionValue = 'true'
GO
ورود / عضویت
شما می توانید تنها با وارد کردن شماره موبایل خود و دریافت کد ورود، وارد حساب کاربری خود شده و یا عضو شوید. همچنین می توانید با وارد کردن ایمیل کاربری خود وارد حساب کاربری خود شوید .
