December 19, 2011 at 2:32 pm
I have about one hundred tables in one schema.
For security reason, I have to divide them in many schema.
I can get specific table with the command:
SELECT T.name, S.name
FROM sys.tables as T
INNER JOIN sys.schemas as S ON S.schema_id = T.schema_id
WHERE S.name='xyz' AND T.name like 'ABC_%'
I tried something (does not work) to transfer them to another schema with the command:
ALTER SCHEMA TheNewSchema TRANSFER
(
SELECT T.name, S.name
FROM sys.tables as T
INNER JOIN sys.schemas as S ON S.schema_id = T.schema_id
WHERE S.name='xyz' AND T.name like 'ABC_%'
)
One hundred tables are not so too many tables. I may do the transfer one by one, but I was wondering if there would be an easy/fast way to do it...
Thanks!
December 19, 2011 at 2:44 pm
Run the code below with results to text. It will generate the statements to transfer schemas.
SELECT 'ALTER SCHEMA TheNewSchema TRANSFER ' + S.name + '.' +T.name +';'
FROM sys.tables as T
INNER JOIN sys.schemas as S ON S.schema_id = T.schema_id
WHERE S.name='xyz' AND T.name like 'ABC_%'
December 20, 2011 at 6:27 am
Wow! SuperDuper! It does exactly what I needed
Thanks a lot 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply