Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webGuru
Starting Member
3 Posts |
 Posted - 2005-03-08 : 15:44:02
|
| How can I dynamically set the field named id to be the identity and then set the seed and increment values. I want to do this for about 76 user tables within 1 database named DCNR. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-08 : 15:59:48
|
| Why?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
webGuru
Starting Member
3 Posts |
Posted - 2005-03-08 : 16:05:14
|
| I developing my test database in the diagram and I forgot to add the identity value to the id field of each table that I created. So... to prevent me from going into 76 different tables I'd prefer to script it out. In 5 years of working with SQL this is a first! The id field is numeric and already set as the primary key in all the tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-08 : 16:19:31
|
| You can not easily do this. To modify a table to have an identity column, you must create a temporary table that contains the layout that you want. Then you move your data into this table, drop your table, then rename the temp one to your table. You then add your indexes and constraints and everything else to this new table. So this isn't going to be easy for 76 tables.Tara |
|
|
|
webGuru
Starting Member
3 Posts |
Posted - 2005-03-08 : 16:25:23
|
| Thanks... I'll do it manually b/c even though no data is in the database I've got relationships and indexes established. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-08 : 16:50:00
|
| If there's no data in the tables yet, why not script out the whole database then in the script file do a find-and-replace: from "[ID] int" to "[ID] int identity(1,1)".Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-08 : 17:05:39
|
| Yes that would be the easiest since there is no data in these tables. Much simpler than doing this in Enterprise Manager one at a time.Tara |
|
|
|
|
|
|
dynamically setting identity in ALL user tables