Stored Procedure Reset IdentitySeed Table,
[sql]
CREATE PROCEDURE [dbo].[Utility.Reset_IdentitySeed]
@SeedTable NVARCHAR(250) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@Column VARCHAR(50)
,@Table VARCHAR(200)
,@SQL NVARCHAR(500)
,@ID INT
DECLARE MyID CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT
a.name
,c.name+'.['+b.name+']' AS tablename
FROM sys.identity_columns a
INNER JOIN sys.tables b ON a.object_id = isnull(OBJECT_ID(@SeedTable),b.object_id)
AND b.object_id = isnull(OBJECT_ID(@SeedTable),b.object_id)
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
OPEN MYID
FETCH NEXT FROM MYID INTO
@Column
,@Table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@SQL = N'select @IDResult=isnull(max('+@Column+'),0) from '+@Table
EXECUTE sp_executesql @SQL
,N'@IDResult int output'
,@IDResult = @ID OUTPUT;
PRINT @Table
DBCC CHECKIDENT(@Table,RESEED,@ID)
FETCH NEXT FROM MYID INTO
@Column
,@Table
END
CLOSE MyID
DEALLOCATE MyID
END
[/sql]
[sql]
CREATE PROCEDURE [dbo].[Utility.Reset_IdentitySeed]
@SeedTable NVARCHAR(250) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@Column VARCHAR(50)
,@Table VARCHAR(200)
,@SQL NVARCHAR(500)
,@ID INT
DECLARE MyID CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT
a.name
,c.name+'.['+b.name+']' AS tablename
FROM sys.identity_columns a
INNER JOIN sys.tables b ON a.object_id = isnull(OBJECT_ID(@SeedTable),b.object_id)
AND b.object_id = isnull(OBJECT_ID(@SeedTable),b.object_id)
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
OPEN MYID
FETCH NEXT FROM MYID INTO
@Column
,@Table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@SQL = N'select @IDResult=isnull(max('+@Column+'),0) from '+@Table
EXECUTE sp_executesql @SQL
,N'@IDResult int output'
,@IDResult = @ID OUTPUT;
PRINT @Table
DBCC CHECKIDENT(@Table,RESEED,@ID)
FETCH NEXT FROM MYID INTO
@Column
,@Table
END
CLOSE MyID
DEALLOCATE MyID
END
[/sql]
Thanks for finally talking about >Stored Procedure Reset
BalasHapusIdentity Seed Table - CodingLite.Com <Liked it!
I'm amazed, I must say. Seldom do I encounter a blog that's both equally educative and entertaining, and
BalasHapuslet me tell you, you've hit the nail on the head. The
problem is something that too few people are speaking intelligently about.
Now i'm very happy I found this during my search for
something relating to this.
Oh my goodness! Impressive article dude! Many thanks, However
BalasHapusI am going through problems with your RSS. I don't understand the reason why I can't subscribe
to it. Is there anybody else getting identical RSS issues?
Anybody who knows the answer can you kindly
respond? Thanx!!
That is a very good tip especially to those fresh to the blogosphere.
BalasHapusBrief but very accurate info… Thank you for sharing
this one. A must read article!
Unquestionably believe that which you stated. Your favorite reason seemed to be on the net the
BalasHapuseasiest thing to be aware of. I say to you, I definitely get
annoyed while people think about worries that they just do not know about.
You managed to hit the nail upon the top and also defined
out the whole thing without having side-effects ,
people could take a signal. Will probably be back to get more.
Thanks