I don’t think it’s a good idea to prefix table names. But in some old systems, it was used heavily for whatever reason.

MS SQL Server doesn’t support renaming everything in one query, but we can generate the queries to (de-)prefix, which we can execute afterwards.

Prefix tables

SELECT 
  'EXECUTE sp_rename ''' 
  + schema_name(schema_id) 
  + '.' 
  + name 
  + ''',''myprefix_' 
  + name + ''';'
 FROM sys.tables T WHERE type = 'U'

replace myprefix_ with whatever you like and execute all/some queries shown to rename all tables.

Remove prefix from tables

To remove the prefix, we need to create a bit different SQL script.

SELECT 
  'EXECUTE sp_rename ''' 
  + schema_name(schema_id) 
  + '.' 
  + name 
  + ''',''' 
  + replace(name, 'myprefix_', '') + ''';'
 FROM sys.tables T WHERE type = 'U'

execute all/some queries shown to remove the prefix.