Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu