Removing relational index parameters when scripting table index in SQL Server 2005

Using the Microsoft.SqlServer.Management.Smo.Scripter object, I create scripts for each of the tables in my database, including indexes / constraints. However, when it generates a script, it includes all index parameters, even if they are default values, for example,

CREATE TABLE [dbo].[AgeGroup](
   [AgeGroupID] [int] NOT NULL),
CONSTRAINT [PK_AgeGroup] PRIMARY KEY CLUSTERED 
(
   [AgeGroupID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

All parameters of the WITH clause of the primary key constraint are set to their default values ​​(at least according to SQL Server Books Online). How can I make the Scripter object not display these parameters?

Here is my code:

Server server = new Server("MYSERVER");

Scripter scripter = new Scripter(server);
scripter.Options.Indexes = true;
scripter.Options.AnsiPadding = true;
scripter.Options.AnsiFile = true;
scripter.Options.Permissions = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.DriAll = true;

Urn[] urns = new Urn[1];

Table t = server.Databases["MYDATABASE"].Tables["AgeGroup"];
urns[0] = t.Urn;

StringCollection sc = scripter.Script(urns);

using (StreamWriter sw = File.CreateText("AgeGroup.Table.sql"))
   foreach (string str in sc)
      sw.WriteLine(str);
+3
source share
2 answers

-, . SMO.Scripting(.. ) , . , script ALTER CREATES, SSMS .

, , , , , . , RegEx . , CREATE script , ") WITH (PAD_INDEX) ON" "ON" (, ).

+3

, , :

private static Scripter CreateScripter ( )

{
    Scripter returnValue = new Scripter(server);
    returnValue.Options.TargetServerVersion = SqlServerVersion.Version90;
    returnValue.Options.TargetDatabaseEngineType = DatabaseEngineType.SqlAzureDatabase;
    returnValue.Options.NoTablePartitioningSchemes = true;
    returnValue.Options.NoFileGroup = true;
    return returnValue;
}

:

CREATE TABLE [dbo].[CacheData](
    [LastFileSystemUpdate] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CacheData] PRIMARY KEY CLUSTERED 
(
    [LastFileSystemUpdate] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
+1

Source: https://habr.com/ru/post/1710800/


All Articles