Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parody
Posting Yak Master
111 Posts |
 Posted - 2012-01-30 : 10:24:39
|
| Before I re-invent the wheel, was wondering if someone could hopefully point me at something I am missing. As part of a sliding window set up I want to dynamically create the staging table at runtime (SSIS) so it is resilient to changes in schema (new indexes etc). This means it has to be an exact schema copy (not a select into)So far I think my best option is an SSIS Transfer objects task. Problem - can't rename table or schema and table switch requires table in same DB/filegroup. Workaround - create login specifically for the ssis task with a diffeent default schema so that everything can be created with the same name.Does anyone have any better ideas short of creating a custom SP to do it? |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2012-01-30 : 10:54:02
|
| Maybe not. For some reason despite turning schema copy off the SSIS task still tries to create the table with the schema, so it doesn't use the login default. Going to look at powershell options too, not done much with that. |
 |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2012-01-31 : 08:09:34
|
| No replies but if anyone is interested here is a little powershell script that will do it. Obviously you could loop and do all objects in a DB etc, but I don't need that.#Get the object we want$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("ServerName")$Object = $Server.Databases["DatabaseName"].Tables["TableName"]#create a new scripter class$Scripter = New-object Microsoft.SqlServer.Management.Smo.Scripter ($Server)#Set scripter options$Scripter.Options.DriAllConstraints = $True$Scripter.Options.IncludeDatabaseContext = $True$Scripter.Options.Indexes = $True$Scripter.Options.ScriptDataCompression = $True$Scripter.Options.ScriptDrops = $False#Generate the script$Scripter.Script($Object) |
|
|
|
|
|
|
Create identical copy of table