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 !!!

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  April 11, 2013 at 3:10 pm

  #1605787

  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

  April 12, 2013 at 12:47 am

  #1605862

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

  https://sqlroadie.com/

• monilps

  SSCommitted

  Points: 1686

  More actions

  April 12, 2013 at 7:33 am

  #1605955

  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: 1004593

  More actions

  April 12, 2013 at 7:36 am

  #1605956

  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: 323522

  More actions

  April 12, 2013 at 7:36 am

  #1605958

  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!