Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

replace the filegroup of each table and generate ddl

Posted on 2016-07-22
16
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

721 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