August 12, 2010 at 1:01 pm
Hey Guys,
I have database with dbo and 30 other schemas. I need to copy tables from dbo to all the other 30 schemas. I know I can do it using generate scripts and then run this on all the 30 schema's by changing the schema names. I wanted to find out if there was any other faster way of doing this, as we will have to do this again and again.
Any help would be appreciated.
Thanks a ton in advance
Nick
August 12, 2010 at 1:35 pm
Why not assign them to a single schema that has read permissions set for all users of the other schemas? Seems like they could all have read-only access to that schema.
The user that maintains the data can have full access to the schema and you won't have to copy the tables out.
August 12, 2010 at 1:56 pm
Thanks for the reply,
I need to copy this as we need to create this for 30 counties, so I need to have 30 copies at one point of time. So, I cannot give them read and on one schema. Any ideas
August 13, 2010 at 9:30 am
Hey Guys,
Any ideas?
August 16, 2010 at 3:15 pm
Questions for you on this:
Are these all read-only?
If so, why would each need a separate schema?
You have separate schemas, or separate databases for each county?
August 16, 2010 at 4:18 pm
to copy table definitions (no indexes or PK's ,etc though) and data use
select * into schema.newtable from dbo.oldtable
for just the table definitions use
select * into schema.newtable from dbo.oldtable where 0 = 1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2010 at 4:33 pm
Here's what I would do. I had a friend doing something similar for clients. He had one "master" database that contained common information and tables for all the clients. This used replication to update each other database so that common stuff was easily moved. Lookup data, etc.
For other common schema tables, he had a blank database that he restored as a new db, then set as a subscriber to the master db.
I'd think about separating like that, not as schemas. Extra databases don't cost anything in terms of licensing, and little in terms of resources. It gives you solid security separation, and it becomes easy to move a client to a new server if needed. Or add a server and new databases for new counties.
It's also a good idea to give each person a DNS entry for their database. You could have county1.mycompany.com, county2.mycompany.com, etc., each pointed to the same IP, and have the logins default to that particular db. Then if you ever moved things, you could move those particular DNS entries to a new instance and the client would never know.
August 17, 2010 at 12:37 am
You'd be better off making the DNS entries CNAME records pointing to the actual computername as this will not change whereas IP addresses can quite easily!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2010 at 7:53 am
Thanks for all the replies,
I already proposed all the things which were mentioned like, separating each schema into separate database. They don't want this to be done. They want the exact replica of the common tables in all the 46 schema's. The only issue I have is to find out the easiest possible way of copying the DDL structure including PK's FK's and indexes and create the same tables with the same structures in all of the 46 schema's. I already did this once and wanted to find out an easy way out. The way I did this is by scripting out all the objects within schema and then manually had to change the schema name for 46 times. I would like to know if there is any other automation or master script which will help me.
Thanks,
Nick
August 17, 2010 at 7:55 am
Thanks, I cannot separate it out to different DB's but I posted a reply. Please read and let me know if you know of any other ideas
August 17, 2010 at 8:18 am
Something like this will generate a script for you to run and create the tables and copy the data across. You'll want to tweak it a bit so that it doesn't return schemas and tables that you don't want to include.
SELECT 'SELECT * INTO [' + s.[name] + '].[' + t.[name] + '] FROM dbo.[' + t.[name] + ']'
FROM sys.schemas s
CROSS JOIN sys.objects t
WHERE s.[name] <> 'dbo'
AND t.[type] = 'U'
AND t.[schema_id] = 1 --dbo
John
August 17, 2010 at 9:04 am
Hi John
this would be a lot better
DECLARE @NewSchema varchar(15)
SELECT @NewSchema = 'pez'
SELECT 'SELECT * INTO [' + @NewSchema + '].[' + t.[name] + '] FROM dbo.[' + t.[name] + ']'
FROM sys.schemas s
inner JOIN sys.objects t on s.schema_id = t.schema_id
WHERE
s.[name] = 'dbo'
AND
t.[type] = 'U'
But still no good as the OP requires all PK's and FK's, etc to be copied. I think SMO could possibly be a valid route here.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2010 at 10:40 pm
how about you script all the objects and run a script (powershell or vb or any other language) to change the schema. we used to change the servername and databasename like this while moving the changes from dev to test to prod env.
August 19, 2010 at 8:53 am
Thanks Guys,
For all the replies, so finally I would say the best way out is still script the objects out and run it in all the schema's. I was trying to automate this but don't think it is possible 😀
August 19, 2010 at 8:55 am
Do it in a cursor. Set up a 1 column table with a list of schemas you want it copied to, script out the table definition, set the schema name to the cursor variable, and set the entire create table statement to a variable and print it.
Something like this:
SET NOCOUNT ON
DECLARE @schema NVARCHAR(50)
DECLARE @SQL NVARCHAR(4000)
DECLARE PrintCreate CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM dbo.schema_list
OPEN PrintCreate
FETCH NEXT FROM PrintCreate into @schema
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = '
CREATE TABLE ['+@schema+'].[SSCTestTable](
[TestColumn1] [nvarchar](255) NULL,
[TestColumn2] [nvarchar](255) NULL,
[TestColumn3] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxSSCTestTable_Column1] ON ['+@schema+'].[SSCTestTable]
(
[TestColumn1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT INTO ['+@schema+'].[SSCTestTable]
SELECT * FROM [dbo].[SSCTestTable]
GO
'
PRINT @SQL
FETCH NEXT FROM PrintCreate into @schema
END
CLOSE PrintCreate
DEALLOCATE PrintCreate
You'll just need to fiddle with the create table code a bit to make sure you have all your quotes in the right place and that it prints out OK, and then copy&paste to a new window and execute. You can include the FK/PK/whatever other DDL requirements in the create table script here.
Again though, I don't know your exact situation so I'm not sure if this suits your needs. I have done similar things to this in the past though, so this is how I'd do it.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply