Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Renameing a column in all tables in a sql 2008 database Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 18, 2011 6:14 AM
Points: 5, Visits: 8
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.
Post #1010732
Posted Tuesday, October 26, 2010 6:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 4,930, Visits: 8,744
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1010739
Posted Tuesday, October 26, 2010 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1010762
Posted Tuesday, October 26, 2010 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
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
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
Post #1010904
Posted Tuesday, October 26, 2010 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1010919
Posted Wednesday, October 27, 2010 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 12:03 PM
Points: 152, Visits: 474
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%'
Post #1011645
Posted Wednesday, October 27, 2010 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
RENAME is a valid command in Oracle, but not SQL; you have to use the sp_rename procedure in this case.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1011670
Posted Wednesday, October 27, 2010 10:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 12:03 PM
Points: 152, Visits: 474
thanks for the clarificaiton
Post #1011696
Posted Monday, January 07, 2013 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 17, 2014 7:22 AM
Points: 228, Visits: 211
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.
Post #1403612
Posted Monday, January 07, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1403626
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse