• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

replace the filegroup of each table and generate ddl

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
bibi92
Asked:
bibi92
  • 9
  • 7
1 Solution
 
bibi92Author Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
I search to replace on the create in the DDL file :
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks
0
SMB Security Just Got a Layer Stronger

WatchGuard acquires Percipient Networks to extend protection to the DNS layer, further increasing the value of Total Security Suite.  Learn more about what this means for you and how you can improve your security with WatchGuard today!

 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
Hello

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

Thanks
0
 
QlemoC++ DeveloperCommented:
$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
 
bibi92Author Commented:
Ok I will remove It and test. Thanks
0
 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
Hello

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

Thanks

Best Regards
0
 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
same result. Only dbo tables are  generated.

Thanks
0
 
bibi92Author Commented:
echo $_.object_name is correct.
I think the problem is on  if ($tbl = $db.Tables[$_.object_name])
Thanks
0
 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
Ok thanks but only the ddl for the last table is generated.

Regards
0
 
QlemoC++ DeveloperCommented:
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
 
bibi92Author Commented:
Thanks a lot regards
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now