define Hierarchy: tool

Showing posts with label tool. Show all posts

Wednesday, March 21, 2012

How to detect diferences between 2 databases

I have a database B on server S and a database B1 o server S1
Is there any tool to give me the diferences between the two databases, new
tables, new fields...
JLobo
www.red-gate.com
Andrew J. Kelly SQL MVP
"JLobo" <JLobo@.discussions.microsoft.com> wrote in message
news:A5AFEF9E-64C0-4CB8-89B4-A56D1E8F4928@.microsoft.com...
>I have a database B on server S and a database B1 o server S1
> Is there any tool to give me the diferences between the two databases, new
> tables, new fields...
> --
> JLobo
|||There are many companies that make tools that do database comparison.
Red Gate is one such company. Their tool - SQL Compare - works very well.
http://www.red-gate.com/
Imceda used to offer Speed Change Manager. http://www.imceda.com Quest
http://www.quest.com/ purchased Imceda a while ago. Quest seems to have
several tools that perform database comparison including CompareRocket for
Visual Studio and Toad for SQL Server.
Keith Kratochvil
"JLobo" <JLobo@.discussions.microsoft.com> wrote in message
news:A5AFEF9E-64C0-4CB8-89B4-A56D1E8F4928@.microsoft.com...
>I have a database B on server S and a database B1 o server S1
> Is there any tool to give me the diferences between the two databases, new
> tables, new fields...
> --
> JLobo

how to design queries graphically?

Are there anytool, which would allow me to design queries graphically, if not design then atlease analyse them graphically.

Tool should atleast show join of 4tables graphically.

Thank You.

SQL Server Management Studio has graphical capabilities - launched in one of two ways:

1.Create a 'New Query' then right-click on the query pane and select 'Design Query in Editor...'.

2.Connect to a server in Object Explorer then navigate to the following node:

Server -> Databases -> <Your database> -> Views

Right-click on 'Views' and select 'New View...' to launch the query editor.

Chris

|||

Try this......

And it's a single query, runs at a single go. I would like to know any s/w / utility (can be third party) which could decode following query.

SELECT '' AS TranNo, 'SC' AS JournalCode, cSupplier AS SupplierCode, cOrderNo AS OrderNo, cWareHouse AS Warehouse, nGrnNo AS GRNNo,

dReceiptDate AS BookEntryDate, vDefaultGLAC AS AccString, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN nTrnsQty * nUnitPrice / [nReceiptsCurrConRate] ELSE nTrnsQty * nUnitPrice * [nReceiptsCurrConRate] END)) AS FOB, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN LandedCost / [nReceiptsCurrConRate] ELSE LandedCost * [nReceiptsCurrConRate] END))

AS LandedCost, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount1, CONVERT(NUMERIC(18,

2), SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc2)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc2) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)

/ [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100) * [nReceiptsCurrConRate] END)

END)) AS Discount2, CONVERT(NUMERIC(18, 2),

SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc3)

/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc3) * [nReceiptsCurrConRate] END)

ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)) * nLineDisc3 / 100) / [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice)

- (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))

* nLineDisc3 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount3

FROM (SELECT PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112) AS dReceiptDate, ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3, SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL

THEN 0 ELSE PMGRNLLC.nBElementalCost END) AS LandedCost

FROM PMPT LEFT OUTER JOIN

PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode AND

PMPT.cSupplier = PMGRNLLC.cSupplierCode AND

PMPT.cOrderNo = PMGRNLLC.cOrderNo AND

PMPT.nLineNo = PMGRNLLC.nLineNo AND

PMPT.cItemCode = PMGRNLLC.cItemCode AND

PMPT.cWareHouse = PMGRNLLC.cWareHouse AND

PMPT.nGrnNo = PMGRNLLC.nGrnNo LEFT OUTER JOIN

ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode LEFT OUTER JOIN

OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',

'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs') AS B ON

PMPT.cCompanyNo = B.CompanyCode AND RTRIM(PMPT.cSupplier) = B.SupplierCode AND

PMPT.cOrderNo = B.OrderNo AND PMPT.cWareHouse = B.WareHouse AND

PMPT.nGrnNo = B.GRNNo

WHERE (PMPT.cCompanyNo = 'GT') AND (PMPT.nGrnNo > 0) AND (PMPT.nTrnsQty > 0) AND

(B.SupplierCode IS NULL)

GROUP BY PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,

PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,

PMPT.dReceiptDate, 112), ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,

PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,

cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3) AS A

WHERE (dReceiptDate >= '20060401') AND (dReceiptDate <= '20070331')

GROUP BY nGrnNo, dReceiptDate, cSupplier, cOrderNo, cWareHouse, nGrnNo, vDefaultGLAC

ORDER BY GRNNo

|||

Although the query looks scary it actually isn't as complex as it looks at first sight, however I've put the query through a 3rd-party refactoring tool for clarity, see below.

It depends what you mean by 'decode'? What are you trying to ascertain by studying a diagram that studying the query wouldn't reveal?

Chris

Code Snippet

SELECT '' AS TranNo,
'SC' AS JournalCode,
cSupplier AS SupplierCode,
cOrderNo AS OrderNo,
cWareHouse AS Warehouse,
nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate,
vDefaultGLAC AS AccString,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN nTrnsQty * nUnitPrice
/ [nReceiptsCurrConRate]
ELSE nTrnsQty * nUnitPrice
* [nReceiptsCurrConRate]
END)) AS FOB,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN LandedCost
/ [nReceiptsCurrConRate]
ELSE LandedCost
* [nReceiptsCurrConRate]
END)) AS LandedCost,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc1)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc1)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount1,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc2)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc2)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount2,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc3)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc3)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount3
FROM (
SELECT PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112) AS dReceiptDate,
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3,
SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL THEN 0
ELSE PMGRNLLC.nBElementalCost
END) AS LandedCost
FROM PMPT
LEFT OUTER JOIN PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode
AND PMPT.cSupplier = PMGRNLLC.cSupplierCode
AND PMPT.cOrderNo = PMGRNLLC.cOrderNo
AND PMPT.nLineNo = PMGRNLLC.nLineNo
AND PMPT.cItemCode = PMGRNLLC.cItemCode
AND PMPT.cWareHouse = PMGRNLLC.cWareHouse
AND PMPT.nGrnNo = PMGRNLLC.nGrnNo
LEFT OUTER JOIN ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode
LEFT OUTER JOIN OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs')
AS B ON PMPT.cCompanyNo = B.CompanyCode
AND RTRIM(PMPT.cSupplier) = B.SupplierCode
AND PMPT.cOrderNo = B.OrderNo
AND PMPT.cWareHouse = B.WareHouse
AND PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT')
AND (PMPT.nGrnNo > 0)
AND (PMPT.nTrnsQty > 0)
AND (B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112),
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3
) AS A
WHERE (dReceiptDate >= '20060401')
AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo,
dReceiptDate,
cSupplier,
cOrderNo,
cWareHouse,
nGrnNo,
vDefaultGLAC
ORDER BY GRNNo

|||

3rd-party refactoring tool is what i'm looking for, it would be much more better if it does graphical representation.

Can you give me any names?

|||

This is a great product for laying out (refactoring) SQL:

http://www.red-gate.com/products/SQL_Refactor/index.htm

However it can't create graphical representations of queries.

To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text.

Chris

Monday, March 19, 2012