SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Renameing a column in all tables in a sql 2008 database


Renameing a column in all tables in a sql 2008 database

Author
Message
colemanflaherty
colemanflaherty
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 9
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.
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9780 Visits: 13349
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27980 Visits: 39922
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!

WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9741 Visits: 10569
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27980 Visits: 39922
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!

SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 548
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%'
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27980 Visits: 39922
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!

SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 548
thanks for the clarificaiton
manik_anu
manik_anu
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 312
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25863 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search