0

We're using SqlPackage to generate scripts via the Script action. Does anyone know a way to get it to exclude indexes? Can't seem to find a way.

The SqlPackage reference gives several /p: properties to do with excluding a whole raft of other object types, which we are using to good effect, but not indexes. Indexes we can only tweak, not exclude, it seems. We're using SQL 2017 but the same goes for SQL 2019.

Has anyone found a way to completely exclude indexes from the script, so that they are just left as they are on the target db, the same as can be done for all the other types of SQL object?

/p: ExcludeObjectTypes=(STRING) A semicolon-delimited list of object types that should be ignored during deployment.

Valid object type names are Aggregates, ApplicationRoles, Assemblies, AsymmetricKeys, BrokerPriorities, Certificates, ColumnEncryptionKeys, ColumnMasterKeys, Contracts, DatabaseRoles, DatabaseTriggers, Defaults, ExtendedProperties, ExternalDataSources, ExternalFileFormats, ExternalTables, Filegroups, FileTables, FullTextCatalogs, FullTextStoplists, MessageTypes, PartitionFunctions, PartitionSchemes, Permissions, Queues, RemoteServiceBindings, RoleMembership, Rules, ScalarValuedFunctions, SearchPropertyLists, SecurityPolicies, Sequences, Services, Signatures, StoredProcedures, SymmetricKeys, Synonyms, Tables, TableValuedFunctions, UserDefinedDataTypes, UserDefinedTableTypes, ClrUserDefinedTypes, Users, Views, XmlSchemaCollections, Audits, Credentials, CryptographicProviders, DatabaseAuditSpecifications, DatabaseScopedCredentials, Endpoints, ErrorMessages, EventNotifications, EventSessions, LinkedServerLogins, LinkedServers, Logins, Routes, ServerAuditSpecifications, ServerRoleMembership, ServerRoles, ServerTriggers.

Please note, we know about /p: DropIndexesNotInSource=True/False and /p: IgnoreIndexOptions=True/False but these are not sufficient.

Reg Edit
  • 6,719
  • 1
  • 35
  • 46
  • I have the same question. Am going to look into writing a deployment contributor and will post back if I find a good solution – Cameron S Apr 11 '23 at 16:52

1 Answers1

-1

I believe you need to use a publish.xml file. See this answer for the rough idea. The question it answers has a sample file. If you set up to not include indexes via the GUI then the file should contain something like <ExcludeIndexes>True</ExcludeIndexes>.

SMM
  • 2,225
  • 1
  • 19
  • 30
  • Interesting, thanks, will take a look. – Reg Edit Apr 21 '22 at 20:56
  • Well, this sounded promising but unfortunately it doesn't do it :( The answer you linked to doesn't actually deal with indexes, and there is no "Exclude Indexes" option that can be set in the wizard that generates the publish profile. The options available are similar or identical to those that can be passed as `/p:` parameters to SqlPackage.exe. I did just try a publish profile with `True` just in case it was somehow missing from the docs, but no, it went ahead and processed indexes same as ever. So unfortunately this is not a solution. Thanks anyway. – Reg Edit Apr 21 '22 at 22:17
  • If you want to consider alternatives I recommend you: [DBATools](https://dbatools.io/) or [SqlServer Module](https://learn.microsoft.com/en-us/powershell/module/sqlserver/?view=sqlserver-ps) – Max Apr 25 '22 at 10:48