Posts Tagged ‘sql’
October 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.
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.
February 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 |
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.
September 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.*/