SQL Server Performance

Alter First Column of All Tables

Discussion in 'General Developer Questions' started by vimalpercy, Mar 9, 2007.

  1. vimalpercy New Member

    Hello I need one help.
    I need to change (Alter) the first Column of All table in my database from integer field to Identity Integer Field.I have 300 tables and it is hard to go and change each table one by one. So need a script which dynamically change it for all tables.

    Thanks and Regards
  2. FrankKalis Moderator

    As a starter you might want to make that changes in Enterprise Manager, but instead of applying these change, let EM generate a change script. Now you can see how many different actions it take to go from INT to INT IDENTITY. You can use this script as a template, but I would do this manually. It is much saver. Are all of your "1st" columns named the same?

    Frank Kalis
    Microsoft SQL Server MVP
  3. vimalpercy New Member

    Thanks Frank Kalis,
    No, the column names are not same.
    other wise i will create a general structure and just add table names to a script.
  4. Adriaan New Member

    Just goes to show that you have to be extremely careful when deciding the data types of your columns. You already have 300 tables and now you find out the unique INT column needs to have the identity property ... where most designers add the IDENTITY property as a matter of reflex.<br /><br />Obviously, somebody has not been paying attention. And perhaps people have been working on the system before they had proper training on database design, or they are not encouraged to apply critical insights to what they are told to do.<br /><br />Do the work to correct the problem, and remember the lesson learnt.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. vimalpercy New Member

    It is an existing old Database created by former colegue 2 years back and i am upgrading that with new Programming language.
    New upgrades includes changing the columns to Identity Column.
    Could you give me the alter table script for one table and i will try to execute that script programatically
    to affect all tables.

    Is it possible to get First Column name, table name of all tables in a database?

    Thanks and regards
  6. FrankKalis Moderator

    Well, if the names are not the same, I really would suggest to do this manually. The probability of something going wrong is very high. Btw, the time it takes to carefully develop and test such a script should not be that much shorter than doing these changes by hand. Sorry, no easy way.

    Frank Kalis
    Microsoft SQL Server MVP
  7. vimalpercy New Member

    Ok, Then let me do it manually.
    Thanks Adriaan and Frank Kalis for your Time and help.
  8. FrankKalis Moderator

    Btw, the "first" column of a table you'll get via<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT * <br /> FROM INFORMATION_SCHEMA.COLUMNS<br /> WHERE ORDINAL_POSITION = 1<br /></font id="code"></pre id="code"><br />At least for those table you have permissions to. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Addition, you also get the "first" column for views. You would need to filter out the views by adding:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsUserTable')=1<br /></font id="code"></pre id="code"><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. vimalpercy New Member

    Thanks FrankKalis For the Script.
    Exactly What i was looking for.
    Now i will try to create the Alter Table script for all these tables by copying the Table and Column -Columns.

    Thanks and thanks a lot again for the help
  10. FrankKalis Moderator

    Good luck! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page