Sql toolbelt

De Banane Atomic
(Redirigé depuis Redgate sql toolbelt)
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="Current" 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

Database '..._SHADOW' does not exist. Make sure that the name is entered correctly

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

Installation with chocolatey

Ps.svg 
# install only sone products
chocolatey install sqltoolbelt --params "/products:'SQL Compare, SQL Data Compare, SQL Prompt, SQL Doc, SQL Test, SSMS Integration Pack, SQL Change Automation'"
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=Sql_toolbelt&oldid=27262 »