resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

sql

Reset Identity Seed to 0

How can I Reset Identity Seed to 0
thnaksTRUNCATE TABLE tablename
Also see DBCC CHECKIDENT in Books Online.
"Music Lover" <music@.my-heart.org> wrote in message
news:uvWV4TiXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> How can I Reset Identity Seed to 0
> thnaks
>
>

Reset IDENTITY seed

Hello,
Can I reset the IDENTITY seed of a Table column without delete/drop the table?
I want to delete all the table rows, restore de seed, and restore abackup made on a XML (using SET IDENTITY_INSERT Table ON)
I cant drop the table due to acount restricctions.
regards,
Edu
You could try
TRUNCATE TABLE MyTable|||Check outDBCC CHECKIDENT.|||Thanks,
DBCC CHECKIDENT. works fine!
Regards,
Edu

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then remov
ed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.
Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then rem
oved. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has
an identity seed.
These records had to be deleted, and I would like to begin the numeric seque
nce at a point as if the records had never been added. E.g., before the inco
rrect records were added, the value of this column was 2500. Two hundred rec
ords were added, then remov
ed. I would like the next value to be 2501, however new records are starting
at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the val
ue, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution
...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that h
as an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at
a point as if the records had never been added. E.g., before the incorrect records
were added, the value of this column was 2500. Two hundred records were added, then
rem
oved. I would like the next value to be 2501, however new records are starting at 2701, afte
r the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the v
alue, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:
> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advanced
Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus
|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus
|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>
|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)
|||this only work for one table
I want it all
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegro ups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>
|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegro ups.com...
>

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advancedUse this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>|||this only work for one table
I want it all :)
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all :)
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> > Use this
> > DBCC CHECKIDENT (tablename, RESEED,1)
> >
>

reset all identity seed in table

I mad a clone of my db and delete all info that I don't need.
how can I reset all identity seed in tables to the beginning numbers?
Tnx in advancedUse this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Use this
DBCC CHECKIDENT (Tablename, RESEED, 30)
Markus|||Have a look at DBCC CHECKIDENT in SQL Books OnLine
"John" <john@.smith.com> wrote in message
news:doe4gc$n4j$1@.news2.netvision.net.il...
> I mad a clone of my db and delete all info that I don't need.
> how can I reset all identity seed in tables to the beginning numbers?
> Tnx in advanced
>|||Use this
DBCC CHECKIDENT (tablename, RESEED,1)|||this only work for one table
I want it all
Tnx
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
> Use this
> DBCC CHECKIDENT (tablename, RESEED,1)
>|||If your tables are not referenced by foreign keys you could give this a try
:-
TRUNCATE TABLE tablename
this will remove all data and reset the identity column to it's seed value.
To run this against every table in your database make use of exec
sp_MSforeachtable
"John" <john@.smith.com> wrote in message
news:doe5om$na6$1@.news2.netvision.net.il...
> this only work for one table
> I want it all
> Tnx
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1135253936.827451.60600@.f14g2000cwb.googlegroups.com...
>

RESEEDING & INCREMENT VALUES

hi,
I am maintaining around 180 tables, out of these-125 have identity values
I need set new SEED value and new increment values for these 125 tables
Is there any easier way to do this?
Thanks,
Soura
you can do something like this
SELECT 'DBCC CHECKIDENT ('+TABLE_NAME +',RESEED , 50 )'
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME) IS NOT NULL
This will reseed the next identity value to 50.
I'm not sure how to change the increment.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:352CE298-4960-43AB-8C9B-A098FF4CAB6F@.microsoft.com...
> hi,
> I am maintaining around 180 tables, out of these-125 have identity
values
> I need set new SEED value and new increment values for these 125 tables
> Is there any easier way to do this?
> Thanks,
> Soura