I don’t think it’s a good idea to prefix table names. But in some old systems, it happens a few times.

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 of course 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.