Geek Answers Handbook

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

I am working on several ASP.NET MVC projects that require a database. Unfortunately, my hosting provider gives me two SQL Server databases, so I want to put the tables of several projects into one database.

However, some of my tables are similarly named, so I may run into some problems. So I'm trying to find a way to change the names of all tables so that they reflect which application they belong to.

Project A currently has the following tables:

  • Table1
  • Table2

In project B, I have the following tables:

  • Table1
  • Table2

I would like to combine tables into one database:

  • ATable1
  • ATable2
  • BTable1
  • BTable2

My questions

  • SQL Server?
  • , LINQ to SQL Table1 ATable1 BTable1 ( , )?
+5
1

,

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

, , LINQ to SQL ( ORM, ) .

SQL , , , . , () SQL Server (b) SQL.

, . dbo ProjectA:

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

, :

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
+10

More articles:


All Articles