Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

replace the filegroup of each table and generate ddl

Posted on 2016-07-22
16
Medium Priority
?
60 Views
Last Modified: 2016-07-25
Hello,

I try to replace the filegroup of each table by one defined in a table settings as part of a move table.
The new filegroup is defined on new_fg_name

$SQLSERVER="TEST\TEST"
$Database="DB"
$Schema="dbo"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$SQLSERVER"
$db=$s.Databases["$Database"]
$liste_table = (& sqlcmd -E -S $SQLSERVER -h -1 -b -W -Q "set nocount on;select object_name from [DBA].[dbo].[ddl_configuration] where new_fg_name <> 'NULL' and  new_fg_name <> ''")

echo $liste_table
foreach($table in $liste_table)
{

$tbls = $db.Tables
$tbl = $tbls  | where { $_.Schema -eq "$Schema" -and $_.Name -eq $table }

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\tbl_ddl.sql"
$scrp.Options.ToFileOnly = $True
$scrp.Script($tbl)
}

How can I resolve, please ?

Thanks

Regards
0
Comment
Question by:bibi92
  • 9
  • 7
16 Comments
 

Author Comment

by:bibi92
ID: 41724509
Hello,

On a ddl file, I search to replace the filegroup with new_file_group defined on a parameters table.


object_schema      object_name                  new_fg_name
dbo                        T_Prices                  FG_CAT
dbo                        T_Services                  FG_CAT

Following the ddl file :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Prices](
      [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 CONSTRAINT [PK_515AF6DE] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Services](
      [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
      [GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK___524F1B17] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

How can I do it?

Thanks

Best regards
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41724588
Are you after a move or create in the DDL file?
I assume the new filegroup should be read from the reference table used to get the tables to work on?
0
 

Author Comment

by:bibi92
ID: 41724597
I search to replace on the create in the DDL file :
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks
0
New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 
LVL 71

Expert Comment

by:Qlemo
ID: 41724840
We will need to change the generated file after the fact.
It puzzles me that you didn't change anything as stated in the prior question, and so you are getting only a single table scripted (if at all).
To make sure it works I have changed the way table names are retrieved.
$SQLSERVER="TEST\TEST"
$Database="DB"
$Schema="dbo"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s  = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSERVER
$db = $s.Databases[$Database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.ToFileOnly = $True


$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
  select object_name, new_fg_name from [DBA].[dbo].[ddl_configuration] where new_fg_name <> 'NULL' and  new_fg_name <> ''
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

$set.Tables[0] | % {
  echo $_.object_name
  if ($tbl = $db.Tables[$_.object_name])
  {
    if ($tbl.Schema -eq $Schema)
    {
      $scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\$($_.object_name)_ddl.sql"
      $scrp.Script($tbl)
      (Get-Content $scrp.Options.FileName) -replace '\[PRIMARY\]', "[$($_.new_fg_name)}]" | Out-File $scrp.Options.FileName
    }
  }
}

Open in new window

0
 

Author Comment

by:bibi92
ID: 41725033
Hello

Thanks this works only for dbo schema. The result is not generated for all tables.

Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41725619
$tbl = $tbls  | where { $_.Schema -eq "$Schema" -and $_.Name -eq $table }

Open in new window

of your code did the restriction of a single schema. I've taken it over.
0
 

Author Comment

by:bibi92
ID: 41725917
Ok I will remove It and test. Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41726001
I've changed the code slightly so you can provide or omit the schema.
$SQLSERVER="TEST\TEST"
$Database="DB"
# $Schema="dbo"
$Schema=$null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s  = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSERVER
$db = $s.Databases[$Database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.ToFileOnly = $True


$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
  select object_name, new_fg_name from [DBA].[dbo].[ddl_configuration] where new_fg_name <> 'NULL' and  new_fg_name <> ''
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

$set.Tables[0] | % {
  echo $_.object_name
  if ($tbl = $db.Tables[$_.object_name])
  {
    if ($Schema -eq $null -or $tbl.Schema -eq $Schema)
    {
      $scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\$($_.object_name)_ddl.sql"
      $scrp.Script($tbl)
      (Get-Content $scrp.Options.FileName) -replace '\[PRIMARY\]', "[$($_.new_fg_name)}]" | Out-File $scrp.Options.FileName
    }
  }
}

Open in new window

0
 

Author Comment

by:bibi92
ID: 41727166
Hello

I have deleted $Schema="dbo", but only dbo tables are generated.

Thanks

Best Regards
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41727178
See my most recent post. I explicitely set Schema = $null to make sure it is not set, and I've added a check for $null to skip checking the schema.
0
 

Author Comment

by:bibi92
ID: 41727401
same result. Only dbo tables are  generated.

Thanks
0
 

Author Comment

by:bibi92
ID: 41727420
echo $_.object_name is correct.
I think the problem is on  if ($tbl = $db.Tables[$_.object_name])
Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41727497
That's correct - we need the schema name, and that is unknown at that point. So we have to expand the complete code. To have unique SQL file names I will add the schema name. Since we can now be certain the tables are found and in the proper schema (if restricted), I've removed the additional checks.
$SQLSERVER="TEST\TEST"
$Database="DB"
# $Schema="dbo"
$Schema=$null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s  = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSERVER
$db = $s.Databases[$Database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.ToFileOnly = $True


$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
  select schema=schema_name(schema_id), object_name, new_fg_name
    from [DBA].[dbo].[ddl_configuration] ddl
    join sys.tables tbls on ddl.object_name = tbls.name
   where new_fg_name <> 'NULL' and  new_fg_name <> ''
     and (tbls.schema_id = schema_id('$Schema') or '$Schema' = '') 
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

$set.Tables[0] | % {
  echo "$($_.schema).$($_.object_name)"
  $scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\$($_.schema)_$($_.object_name)_ddl.sql"
  $scrp.Script($db.Tables[$_.object_name,$_.schema])
  (Get-Content $scrp.Options.FileName) -replace '\[PRIMARY\]', "[$($_.new_fg_name)}]" | Out-File $scrp.Options.FileName
}

Open in new window

0
 

Author Comment

by:bibi92
ID: 41727504
Ok thanks but only the ddl for the last table is generated.

Regards
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 41727574
You should get a SQL syntax error regarding 'schema' in the first SQL, and PS errors when accessing the Tables collection - and no file at all.
Slightly changed the script to cope with that.
$SQLSERVER="TEST\TEST"
$Database="DB"
# $Schema="dbo"
$Schema=$null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s  = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSERVER
$db = $s.Databases[$Database]

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.ToFileOnly = $True

$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
  select schema_name=schema_name(schema_id), object_name, new_fg_name
    from [DBA].[dbo].[ddl_configuration] ddl
    join sys.tables tbls on ddl.object_name = tbls.name
   where new_fg_name <> 'NULL' and  new_fg_name <> ''
     and (tbls.schema_id = schema_id('$Schema') or '$Schema' = '') 
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

$set.Tables[0] | % {
  echo "$($_.schema_name).$($_.object_name)"
  $scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\$($_.schema_name)_$($_.object_name)_ddl.sql"
  $scrp.Script($db.Tables.Item($_.object_name,$_.schema_name))
  (Get-Content $scrp.Options.FileName) -replace '\[PRIMARY\]', "[$($_.new_fg_name)]" | Out-File $scrp.Options.FileName
}

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 41727648
Thanks a lot regards
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question