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
Solved

replace the filegroup of each table and generate ddl

Posted on 2016-07-22
16
35 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 69

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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 69

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 69

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 69

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 69

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 69

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 69

Accepted Solution

by:
Qlemo earned 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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