Post reply

Renameing a column in all tables in a sql 2008 database

  • October 26, 2010 at 6:39 am

    #225449

    I have to rename a column and add columns to all tables ( could be up to 300 tables ) in a database is the a query that can do this or a quick way to do this.

  • October 26, 2010 at 6:47 am

    #1242204

    You can use sp_rename:

    EXEC sp_rename 'schema.table.name', 'newname', 'COLUMN'

    As for adding new columns, you can use ALTER TABLE:

    ALTER TABLE tablename ADD [column definition]

    -- Gianluca Sartori

  • October 26, 2010 at 7:11 am

    #1242225

    you can also use the metadata to generate the statements you need to run...potentially 300 commands you said.

    you did not provide any specific details, so here's a scenario to use as a model.

    the column "CRDT" exists in a zillion tables in the database.

    the description is really crappy, so we are renaming the column in every table to ''CREATED_DATE'' so it is more descriptive.

    here's a code example to generate all those commands, which can be examined and run seperately:

    select

    'EXEC sp_rename ''' + QUOTENAME(sc.name) + '.' + QUOTENAME(tb.name) + '.' + QUOTENAME(col.name) + ''', ''CREATED_DATE'', ''COLUMN'' ' AS SQLCMD,

    sc.name,tb.name,col.name

    from sys.tables tb

    inner join sys.schemas sc on tb.schema_id = sc.schema_id

    inner join sys.columns col on tb.object_id = col.object_id

    where col.name = 'CRDT'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 26, 2010 at 9:14 am

    #1242357

    Lowell (10/26/2010)

    here's a code example to generate all those commands, which can be examined and run seperately:

    Lowell, I'd recommend one change to your code - use the QuoteName() function for the schema/table names. For dynamically generated code like this, better to make it bullet-proof.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 26, 2010 at 9:23 am

    #1242370

    WayneS (10/26/2010)

    Lowell, I'd recommend one change to your code - use the QuoteName() function for the schema/table names. For dynamically generated code like this, better to make it bullet-proof.

    awesome idea, i updated my post above with your most excellent suggestion!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 27, 2010 at 9:28 am

    #1242981

    Could we use something like this ??

    SELECT 'ALTER TABLE ' +

    t.Table_Name +

    ' Rename column ' +

    c.Column_Name +

    ';'

    FROM Information_Schema.tables t

    INNER JOIN Information_Schema.columns C

    ON t.Table_name = c.Table_name

    WHERE c.Column_Name LIKE '%What u want%'

  • October 27, 2010 at 9:48 am

    #1243001

    RENAME is a valid command in Oracle, but not SQL; you have to use the sp_rename procedure in this case.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 27, 2010 at 10:16 am

    #1243026

    thanks for the clarificaiton

  • January 7, 2013 at 7:43 am

    #1574313

    SQLTestUser (10/27/2010)

    thanks for the clarificaiton

    Actually i am new for table design. i added one new column like [insred_name] in existing table with spelling mistake. then i changed that column name llike [insured_name] with brases. Once again i changed to insured_name. but now i got the error like 'Ambiguous column name'. anyone please tell me how can i solve this?????

    Manik
    You cannot get to the top by sitting on your bottom.

  • January 7, 2013 at 7:58 am

    #1574323

    manik123 (1/7/2013)

    SQLTestUser (10/27/2010)

    thanks for the clarificaiton

    Actually i am new for table design. i added one new column like [insred_name] in existing table with spelling mistake. then i changed that column name llike [insured_name] with brases. Once again i changed to insured_name. but now i got the error like 'Ambiguous column name'. anyone please tell me how can i solve this?????

    For starters you should begin your own thread instead of hijacking another one. It sounds as though you have a query that insured_name as column in more than 1 table? When this is the case you have to tell sql which table you want the data from. To do this you need to qualify your column in your select statement with the table name or alias and a period.

    alias.insured_name

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • January 7, 2013 at 8:12 am

    #1574334

    kk thanks for your reply.... but i try to ask how to drop that column.... i cannot drop that column.... nice article very useful to me.......:-)

    Manik
    You cannot get to the top by sitting on your bottom.

  • January 7, 2013 at 8:38 am

    #1574349

    manik123 (1/7/2013)

    kk thanks for your reply.... but i try to ask how to drop that column.... i cannot drop that column.... nice article very useful to me.......:-)

    I don't understand. You don't have to drop the column, you just have to refer to it correctly. Perhaps if you posted the ddl of the existing table and the t-sql you are trying to run it would help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply