Accidently Named all tables in my database starting with a number

Question

I've accidently named all my tables (100+) in my sql database with the prefix 2019_

Wasn't aware this was not a good idea until just now (just returned to SQL after 2 years)

Is there any way to bulk swap it to a suffix or even delete all the tables? I can't even seem to open them. Really don't want to go through them all manually.

Appologies if this is a silly question, potentially googling the wrong terms

Thanks!

Answer 1

In your situation, I would probably use a script something like this to generate all the sp_rename commands for me:

SELECT SCHEMA_NAME(schema_id) as schemaname, name as tablename, 
cmd = 'EXEC sp_rename ''' + SCHEMA_NAME(schema_id) + '.' + name + ''', ''' 
  + SUBSTRING(name, 6, 99) + ''';'
FROM sys.tables
WHERE type = 'U'
AND is_ms_shipped = 0
AND name LIKE '2019[_]%'

This takes the name from the sys.tables system table, and finds all the user-created tables with a name starting with 2019_. It generates a list of sp_rename commands, which you can copy into a new SSMS window and carefully review to make sure they look correct before running.

Note that this is going to break any other objects (views, triggers, stored procs, etc.) that refer to these tables, so I hope this database is very early in development.

Answer 2

Part-answers originally left as comments:

eagle275: A table-name starting with a number is still valid SQL, but to "read" from SQL Server, you write it as [dbo].[table_name].

strawberry: First, make a backup. Then, you could generate a script of the database and simple edit that with your favourite text editor.

tibor-karaszi: Since you say "even delete all the tables", perhaps it is easier to just delete the database and create a new? It depends of course if there are other things in the database that you want to keep.

david-spillett: Any views, stored procedures, triggers, functions, etc. within the DB itself will also need table references updating. This could be a significant task and faf to automate if the there are object names other than those of tables that aren't being updated. If this DB is live in any way you might be better leaving the mistake in place for now unless you have budget for a fair chunk of testing time!