Is it possible to automatically prefix all table names in SQL Server and in LINQ to SQL?

Question

I'm working on a few ASP.NET MVC projects which all require database functionality. Unfortunately, my hosting provider only gives me two SQL Server databases to work with, so I want to put the tables of multiple projects into a single database.

However, some of my tables are similarly named, so I might run into some issues. Thus, I'm trying to find a way to change the names of all the tables so that they reflect what application they belong to.

Currently, I have the following tables in Project A:

• Table1
• Table2

In Project B, I have these tables:

• Table1
• Table2

I would like to combine the tables into a single database:

• ATable1
• ATable2
• BTable1
• BTable2

---

My Questions

1. How can I automatically add a prefix to the names of all of the tables inside of a SQL Server database?
2. Is it possible to have LINQ to SQL automatically map Table1 from my code into ATable1 or BTable1 (depending on what application it is)?

Answer 1

Put the tables in separate schemas, e.g.

create schema ProjectA;
create schema ProjectB;
create table ProjectA.Table1 (...);
create table ProjectA.Table2 (...);
create table ProjectB.Table1 (...);
create table ProjectB.Table2 (...);

I haven't tested this myself, but I would be very surprised if LINQ to SQL (and any other ORM for that matter) didn't store and correctly make use of the schema names.

If you write your own SQL commands manually, be aware that you have to quote the schema name on all objects unless it is the default schema for your current database user. This could cause a problem if (a) you have only been allocated one SQL Server login, and (b) you have a lot of existing SQL.

To move existing tables into a new schema, e.g. tables in dbo into ProjectA:

alter schema ProjectA transfer dbo.Table1;
alter schema ProjectA transfer dbo.Table2;

To do an automated rename, you could write a simple loop:

declare objectsCursor cursor local fast_forward for
    select o.name as objectname, s.name as schemaname
    from sys.objects as o
    inner join sys.schemas as s on o.schema_id = s.schema_id
    -- Alter these filters depending on what you want to convert
    where s.name = 'dbo'
    and o.type = 'U'
declare @objectname sysname, @schemaname sysname, @sql nvarchar(max)
open objectsCursor
fetch next from objectsCursor into @objectname, @schemaname
while @@fetch_status = 0 begin
    select @sql = N'alter schema ProjectA transfer ' + quotename(@schemaname) + '.' + quotename(@objectname)
    execute (@sql)
    fetch next from objectsCursor into @objectname, @schemaname
end
close objectsCursor