Add prefix to all table name

Question

Add prefix to all table name

monilps

SSCommitted

Points: 1686
More actions
April 11, 2013 at 2:56 pm

#273326

Hi,
I would like add same prefix to all table names.
For Example:
TableA
TableB
Result Needed:
aaa_TableA
aaa_TableB
I would appreciate some input.
Thanks in advance !!!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

monilps (4/11/2013)
Hi,
I would like add same prefix to all table names.
For Example:
TableA
TableB
Result Needed:
aaa_TableA
aaa_TableB
I would appreciate some input.
Thanks in advance !!!

Why do you want prefixes on all your tables? This is generally not a best practice. If the idea is to group them it would be better to use a schema instead of prefixes.

If you are deadset on doing this then I would use sys.tables to help you build the sql.

_______________________________________________________________

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/

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

SELECT 'aaa_' + name FROM sys.tables T WHERE type = 'U'

https://sqlroadie.com/

monilps SSCommitted Points: 1686 More actions · Answer 3

Sorry, I was not clear enough. I need to update table name.

From TableA, TableB to aaa_TableA, aaa_TableB.

Thanks.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

monilps (4/12/2013)
Sorry, I was not clear enough. I need to update table name.
From TableA, TableB to aaa_TableA, aaa_TableB.
Thanks.

You do realize that this will break every piece of code you have, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 5

monilps (4/12/2013)
Sorry, I was not clear enough. I need to update table name.
From TableA, TableB to aaa_TableA, aaa_TableB.
Thanks.

well you alreayd got the advice that this is a bad idea, but here you go: you know that this will break any views, functions or procedures referencing these tables, of course:

SELECT

'EXECUTE sp_rename '''

+ schema_name(schema_id)

+ '.'

+ name

+ ''',''aaa_'

+ name + ''';'

FROM sys.tables T WHERE type = 'U'

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!