Posts Tagged ‘sql’

How to open and edit an erx / ER diagram file in Toad

Oct 21st, 2014

Creating an ER diagram in Toad is simple. Opening is not. Searching the intertoobs gives the result, mostly, to use Toad data modeler. My Toad data modeler version 5.1.1.12 simply refuses to let me add new tables to a diagram.

So here is my solution.
First ditch Toad data modeler.

Create the ER diagram in Toad->menu->Database->Report->ErDiagram.
Manipulate.
Save.

ER diagram menu in Toad.

Open an existing ER diagram through Toad->menu->Database->Report->ErDiagram as above.
But instead of editing look on the ugly toolbar for Open file.
Now one can edit the ER diagram.

Open file button in numerous tool bars in Toad.

List the size of tables and stuff in a Sqlserver

Feb 4th, 2013

A simple solution copied from here is

1
sp_msforeachtable "sp_spaceused '?'"

an almost as simple is the one below copied from here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
SET NOCOUNT ON

CREATE TABLE #TBLSize
(Tblname VARCHAR(80),
TblRows INT,
TblReserved VARCHAR(80),
TblData VARCHAR(80),
TblIndex_Size VARCHAR(80),
TblUnused VARCHAR(80))

DECLARE @DBname VARCHAR(80)
DECLARE @tablename VARCHAR(80)

SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename

-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

SELECT CAST(Tblname AS VARCHAR(30)) 'Table',
CAST(TblRows AS VARCHAR(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)',
CAST(TblData AS VARCHAR(14)) 'Data Space',
CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space',
CAST(TblUnused AS VARCHAR(14)) 'Unused Space'
FROM #tblSize
ORDER BY 'Total Space (KB)' DESC

PRINT ''
PRINT 'By Table Name Alphabetical'

SELECT CAST(Tblname AS VARCHAR(30)) 'Table',
CAST(TblRows AS VARCHAR(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)',
CAST(TblData AS VARCHAR(14)) 'Data Space',
CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space',
CAST(TblUnused AS VARCHAR(14)) 'Unused Space'
FROM #tblSize
ORDER BY 'Table'

DROP TABLE #TblSize

Script data in Sqlserver

May 17th, 2011

I don’t know for how long this has been possible but I guess my years of whining about lack of data scripting capabilities in Sqlserver management studio is moot.

I found an article which describes the hidden setting.  I wonder for how long the possibility has been there.

http://www.kodyaz.com/articles/how-to-script-data-in-sql-server-2011.aspx

I remember Sqlbase scripting its own data back in last century.  I remember myself writing sprocs for the same for Sqlserver.  I remember myself hunting scripts for the same on the web.  I now foresee a future with lots of clicketyclick – the GUI of MS’s Sqlserver tools is way too fiddly for my taste.

Select Distinct is considered bad

Sep 15th, 2008

Correction; Considered a warning.

Here is a good rule-of-thumb: If you have to write an SQL with Select Distinct, do adjust your chair, get a cup of coffee, walk around the office but above all talk to someone else because Select Distinct is a symptom of something being wrong, either in the database or in your solution.

/* Select Distinct is normally not needed when your database is lagom normalized. But if you do need a Select Distinct then this is a very good example of a situation that needs to be documented.*/