Sql toolbelt

De Banane Atomic
Aller à la navigationAller à la recherche

SQL Toolbelt

Groupement de produits Redgate pour le dévelopement et le déploiement sur SQL Server.

SQL Prompt

Formater le code:

  • Ctrl + K, Y
  • SQL Prompt → Format SQL
CustomFormatStyle.sqlpromptstylev2 
<?xml version="1.0" encoding="utf-8"?>
<LayoutOptions xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
               xmlns="http://schemas.datacontract.org/2004/07/Format.Engine.Formatting.Options">
  <AlignCaseElseToWhen>true</AlignCaseElseToWhen>
  <AlignToTab>false</AlignToTab>
  <BetweenAndAlignment>ToBetween</BetweenAndAlignment>
  <BooleanOperatorAlignment>AsPartOfList</BooleanOperatorAlignment>
  <BooleanOperatorBreakType>ChopAlways</BooleanOperatorBreakType>
  <BreakAssignmentIfLong>true</BreakAssignmentIfLong>
  <BreakOnConstraints>true</BreakOnConstraints>
  <BuiltInDataTypeCasing>Uppercase</BuiltInDataTypeCasing>
  <BuiltInFunctionCasing>Uppercase</BuiltInFunctionCasing>
  <CaseEndAlignment>ToCase</CaseEndAlignment>
  <CaseExpressionAlignment>Tabbed</CaseExpressionAlignment>
  <CaseWhenElseAlignment>Tabbed</CaseWhenElseAlignment>
  <ClauseAlignment>ToStatement</ClauseAlignment>
  <ClauseIndentation>0</ClauseIndentation>
  <ClosingParenthesisAlignment>ToOpeningBracket</ClosingParenthesisAlignment>
  <CollapseCaseExpressionIfShort>true</CollapseCaseExpressionIfShort>
  <CollapseCaseIfShortCharacterCount>160</CollapseCaseIfShortCharacterCount>
  <CollapseIfShortControlFlowCharacterCount>78</CollapseIfShortControlFlowCharacterCount>
  <CollapseIfShortDdlCharacterCount>75</CollapseIfShortDdlCharacterCount>
  <CollapseIfShortDmlCharacterCount>120</CollapseIfShortDmlCharacterCount>
  <CollapseIfShortParenthesesContentsCharacterCount>160</CollapseIfShortParenthesesContentsCharacterCount>
  <CollapseIfShortSubqueryCharacterCount>120</CollapseIfShortSubqueryCharacterCount>
  <CollapseShortControlFlowStatements>false</CollapseShortControlFlowStatements>
  <CollapseShortDdlStatements>true</CollapseShortDdlStatements>
  <CollapseShortDmlStatements>true</CollapseShortDmlStatements>
  <CollapseShortParenthesesContents>true</CollapseShortParenthesesContents>
  <CollapseShortSubqueries>true</CollapseShortSubqueries>
  <CommaAlignment>ToList</CommaAlignment>
  <ConstraintColumnsBreakType>ChopIfLongOrMultipleItems</ConstraintColumnsBreakType>
  <CreateAlterClosingParenthesisAlignment>ToOpeningBracket</CreateAlterClosingParenthesisAlignment>
  <CreateAlterIndentParenthesisedExpression>true</CreateAlterIndentParenthesisedExpression>
  <CreateAlterOpeningParenthesisAlignment>ToStatement</CreateAlterOpeningParenthesisAlignment>
  <CreateAlterOpeningParenthesisBreakType>ChopAlways</CreateAlterOpeningParenthesisBreakType>
  <CreateAlterParenthesisLayout>ExpandedToStatement</CreateAlterParenthesisLayout>
  <CreateAlterParenthesisedExpressionBreakType>ChopAlways</CreateAlterParenthesisedExpressionBreakType>
  <CreateAlterPlaceClosingParenthesisOnNewLine>true</CreateAlterPlaceClosingParenthesisOnNewLine>
  <CreateTableBreakBeforeFirstListItem>true</CreateTableBreakBeforeFirstListItem>
  <CteAsAlignment>ToStatement</CteAsAlignment>
  <CteColumnsOpeningParenthesisAlignment>ToStatement</CteColumnsOpeningParenthesisAlignment>
  <CteColumnsOpeningParenthesisBreakType>Never</CteColumnsOpeningParenthesisBreakType>
  <CteContentsOpeningParenthesisAlignment>ToStatement</CteContentsOpeningParenthesisAlignment>
  <CteContentsOpeningParenthesisBreakType>Never</CteContentsOpeningParenthesisBreakType>
  <CteContentsParenthesisLayout>ExpandedToStatement</CteContentsParenthesisLayout>
  <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment>
  <CteContentsParenthesisedExpressionBreakType>Never</CteContentsParenthesisedExpressionBreakType>
  <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType>
  <FirstCreateProcedureParamBreakType>ChopIfLongOrMultipleItems</FirstCreateProcedureParamBreakType>
  <FirstListItemBreakType>Never</FirstListItemBreakType>
  <FromClauseFirstItemBreakType>Never</FromClauseFirstItemBreakType>
  <FunctionArgumentsBreakType>ChopAlways</FunctionArgumentsBreakType>
  <GlobalVariablesCasing>LeaveAsIs</GlobalVariablesCasing>
  <GroupByOrderByFirstItemBreakType>Never</GroupByOrderByFirstItemBreakType>
  <InValuesFirstItemBreakType>SimpleWrap</InValuesFirstItemBreakType>
  <InValuesOpeningParenthesisAlignment>ToStatement</InValuesOpeningParenthesisAlignment>
  <InValuesOpeningParenthesisBreakType>Never</InValuesOpeningParenthesisBreakType>
  <InValuesSubsequentListItemsBreakType>SimpleWrap</InValuesSubsequentListItemsBreakType>
  <IndentBeginEndKeywords>false</IndentBeginEndKeywords>
  <IndentBlockContents>true</IndentBlockContents>
  <IndentCteContents>true</IndentCteContents>
  <IndentCteName>false</IndentCteName>
  <IndentDdlClauses>false</IndentDdlClauses>
  <IndentInsertColumnListsParenthesisedExpression>true</IndentInsertColumnListsParenthesisedExpression>
  <IndentInsertValuesListsParenthesisedExpression>true</IndentInsertValuesListsParenthesisedExpression>
  <IndentJoinTable>false</IndentJoinTable>
  <IndentListItems>true</IndentListItems>
  <IndentParenthesisedExpression>false</IndentParenthesisedExpression>
  <InsertColumnListParenthesisLayout>ExpandedToStatement</InsertColumnListParenthesisLayout>
  <InsertEmptyLineBetweenJoins>false</InsertEmptyLineBetweenJoins>
  <InsertValuesListParenthesisLayout>CompactToStatement</InsertValuesListParenthesisLayout>
  <IsMigratedStyle>false</IsMigratedStyle>
  <JoinBreakType>ChopAlways</JoinBreakType>
  <JoinClauseAlignment>Tabbed</JoinClauseAlignment>
  <JoinConditionBreakType>Never</JoinConditionBreakType>
  <JoinOnAlignment>TabbedFromJoin</JoinOnAlignment>
  <JoinOnConditionAlignment>ToOnKeyword</JoinOnConditionAlignment>
  <Name>Pricing</Name>
  <NewLineAfterTopRowFilter>true</NewLineAfterTopRowFilter>
  <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter>
  <NewLineForCorrelatedTableSource>true</NewLineForCorrelatedTableSource>
  <NumberOfEmptyLinesAfterBatchSeparator>1</NumberOfEmptyLinesAfterBatchSeparator>
  <NumberOfEmptyLinesBetweenStatements>0</NumberOfEmptyLinesBetweenStatements>
  <NumberOfSpacesInTab>4</NumberOfSpacesInTab>
  <OpeningParenthesisAlignment>ToStatement</OpeningParenthesisAlignment>
  <OpeningParenthesisBreakType>ChopAlways</OpeningParenthesisBreakType>
  <OptionsVersion>12</OptionsVersion>
  <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements>
  <ParenthesisLayout>CompactSimple</ParenthesisLayout>
  <ParenthesisedExpressionAlignment>TabbedFromOpeningBracket</ParenthesisedExpressionAlignment>
  <ParenthesisedExpressionBreakType>ChopAlways</ParenthesisedExpressionBreakType>
  <PlaceBeginOnNewLine>true</PlaceBeginOnNewLine>
  <PlaceCaseElseOnNewLine>true</PlaceCaseElseOnNewLine>
  <PlaceCaseEndOnNewLine>true</PlaceCaseEndOnNewLine>
  <PlaceCaseExpressionOnNewLine>true</PlaceCaseExpressionOnNewLine>
  <PlaceCaseFirstWhenOnNewLine>Always</PlaceCaseFirstWhenOnNewLine>
  <PlaceClosingParenthesisOnNewLine>true</PlaceClosingParenthesisOnNewLine>
  <PlaceCommasBeforeListItems>false</PlaceCommasBeforeListItems>
  <PlaceJoinOnKeywordOnANewLine>true</PlaceJoinOnKeywordOnANewLine>
  <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine>
  <PreferBreakBeforeAsKeyword>false</PreferBreakBeforeAsKeyword>
  <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword>
  <PreferBreakBeforeBetweenKeyword>true</PreferBreakBeforeBetweenKeyword>
  <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator>
  <PreferBreakBeforeCteName>true</PreferBreakBeforeCteName>
  <PreferBreakBeforeEquals>true</PreferBreakBeforeEquals>
  <PreferBreakBeforeInsertTable>false</PreferBreakBeforeInsertTable>
  <PreferBreakBeforeThenKeyword>false</PreferBreakBeforeThenKeyword>
  <PreserveLineSpacingBetweenStatements>true</PreserveLineSpacingBetweenStatements>
  <PreserveLineSpacingWithinStatements>false</PreserveLineSpacingWithinStatements>
  <ReservedKeywordsCasing>Uppercase</ReservedKeywordsCasing>
  <SemicolonWhitespace>None</SemicolonWhitespace>
  <SpaceBeforeUnits>false</SpaceBeforeUnits>
  <SpaceBetweenDataTypeAndParameters>false</SpaceBetweenDataTypeAndParameters>
  <SpaceInsideEmptyParentheses>false</SpaceInsideEmptyParentheses>
  <SpacesAroundArithmeticOperators>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundArithmeticOperators>
  <SpacesAroundCommas>
    <m_After>true</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundCommas>
  <SpacesAroundComparisonOperators>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundComparisonOperators>
  <SpacesAroundFunctionCallArguments>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundFunctionCallArguments>
  <SpacesAroundFunctionCalls>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundFunctionCalls>
  <SpacesAroundInPredicateContents>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundInPredicateContents>
  <SpacesAroundParentheses>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundParentheses>
  <SpacesAroundParenthesesContents>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundParenthesesContents>
  <SpacesOrTabs>OnlySpaces</SpacesOrTabs>
  <SubsequentInsertColumnsListItemsBreakType>ChopAlways</SubsequentInsertColumnsListItemsBreakType>
  <SubsequentInsertValuesListItemsBreakType>SimpleWrap</SubsequentInsertValuesListItemsBreakType>
  <SubsequentListItemsBreakType>ChopAlways</SubsequentListItemsBreakType>
  <UseGlobalListOptionsForDmlStatements>false</UseGlobalListOptionsForDmlStatements>
  <UseObjectDefinitionCase>true</UseObjectDefinitionCase>
  <VerticallyAlignAliases>false</VerticallyAlignAliases>
  <VerticallyAlignClauseItems>false</VerticallyAlignClauseItems>
  <VerticallyAlignColumnDefinitions>false</VerticallyAlignColumnDefinitions>
  <VerticallyAlignComparisonOperators>false</VerticallyAlignComparisonOperators>
  <VerticallyAlignDataTypes>false</VerticallyAlignDataTypes>
  <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable>
  <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments>
  <VerticallyAlignListItems>true</VerticallyAlignListItems>
  <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType>
  <WrapColumn>160</WrapColumn>
  <WrapLongLines>true</WrapLongLines>
</LayoutOptions>

Restore pinned tabs

SQL Prompt restore les onglets précédement ouvert mais le status pinned est perdu.

SQL Compare

Ps.svg 
& "C:\Program Files (x86)\Red Gate\SQL Compare 14\SQLCompare.exe" /S1:srv1 /db1:db1 /S2:srv2 /db2:db2
                                                                  /include:table,view,storedprocedure,function,userdefinedtype
                                                                  /exclude:table:\[TableName\]
                                                                  /o:default,ip  # ip = IgnorePermissions
                                                                  /include:Identical  # don't throw exception if the compared db are identical
                                                                  /sf:"C:\temp\out.sql" /force  # ScriptFile
                                                                  /sync  # apply the changes on db2
                                                                  /verbose

SQL Data Compare

Permet de comparer les données de 2 bdd et de les aligner.

Si SQL Data Compare n'est pas accessible depuis le menu, SQL Search → icone blanche sur fond rouge → SQL Data Compare
Ps.svg 
# compare the data and apply the changes
"C:\Program Files (x86)\Red Gate\SQL Data Compare 14\SQLDataCompare.exe" /S1:server1 /db1:db1 /S2:server2 /db2:db2
                                                                         /include:Identical  # don't throw exception if the compared db are identical
                                                                         /sync  # apply the changes on db2
                                                                         /verbose

SQL Change Automation

Config file:

MyProject.sqlproj.user 
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabase>MyDb</TargetDatabase>
    <TargetConnectionString>Data Source=(local)\;Initial Catalog=MyDb;Integrated Security=True;Pooling=False;Encrypt=False;Trust Server Certificate=True;Multi Subnet Failover=False</TargetConnectionString>
  </PropertyGroup>
</Project>

SQL Source Control

Migration scripts

Création d'un script de migration de données.

La table RedGateLocal.DeploymentMetadata contient la liste des scripts de migration qui ont été appliqué.

Errors

Error Inserting Semicolons durant Format

SQL Prompt → Options → Format → Style → décocher Insert semicolons

The 'SqlSourceControlPackage' package did not load correctly

Licence

Ps.svg 
# désactiver la licence
& 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /deactivateSerial

# activer la licence
& 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /activateSerial:001-123-456789-ABCD
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=Sql_toolbelt&oldid=24241 »