Solved

replace the filegroup of each table and generate ddl

Posted on 2016-07-22
16
27 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
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
I search to replace on the create in the DDL file :
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
Hello

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

Thanks
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
$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
Comment Utility
Ok I will remove It and test. Thanks
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:bibi92
Comment Utility
Hello

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

Thanks

Best Regards
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
same result. Only dbo tables are  generated.

Thanks
0
 

Author Comment

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

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
Ok thanks but only the ddl for the last table is generated.

Regards
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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
Comment Utility
Thanks a lot regards
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now