Documentation

[randusoleis]

In regard to documentation of databases, is there a way to document the database structure? Access 2000 has the Analyze tool....what about SQL7.0 Randusoleis.....

 

[AshleyRickards]

Below is some code that will document all your tables , record counts, table size etc .. paste into query analyzer and run on your database. You can also generate scripts which will give you all your table structures.

-----
SET NOCOUNT ON

DECLARE @ColumnName char( 255 ),
@Output char( 255 ),
@Heading char( 80 ),
@Padding char( 80 ),
@Table char( 30 ),
@RowBytes char( 30 ),
@TempData char( 30 ),
@TempStr char( 30 ),
@TotalKB char( 30 ),
@TotalDBSize numeric( 10, 0 ),
@RowCount numeric( 10, 0 ),
@ColumnLength int,
@TotalColumns int,
@TotalColSize int,
@TotalTables int,
@PadLength1 int,
@PadLength2 int,
@TotalRows int,
@ColCount int,
@ColSize int,
@RowSize int,
@TableID int,
@Length int,
@Done bit

SELECT @ColumnLength = 0,
@TotalColumns = 0,
@TotalColSize = 0,
@TotalTables = 0,
@TotalDBSize = 0,
@PadLength1 = 0,
@PadLength2 = 0,
@TotalRows = 0,
@ColCount = 0,
@RowCount = 0,
@ColSize = 0

SELECT @Padding = ' '

SELECT @Heading = 'Table Size Report for '
+ RTRIM( DB_Name() )
+ ' as of '
+ RTRIM( CONVERT( char( 19 ), GetDate() ) )

SELECT @PadLength1 = ( ROUND( ( 79 - DATALENGTH( RTRIM( @Heading ) ) ), 0 ) / 2 )
SELECT @PadLength2 = @PadLength1

IF ( ( ( @PadLength1 * 2 ) + DATALENGTH( RTRIM( @Heading ) ) ) > 78 )
BEGIN
SELECT @PadLength2 = @PadLength1 - 2
END

SELECT @Output = '['
+ SUBSTRING( @Padding, 1, @PadLength1 )
+ RTRIM( @Heading )
+ SUBSTRING( @Padding, 1, @PadLength2 )
+ ']'
PRINT '==============================================================================='
PRINT @Output
PRINT '==============================================================================='
PRINT '| Table | Columns | Size | Rows | Bytes |'
PRINT '-------------------------------------------------------------------------------'

DECLARE TableScan CURSOR FOR
SELECT Name,
ID
FROM SysObjects
WHERE Type = 'U'
ORDER BY Name

OPEN TableScan

FETCH NEXT FROM TableScan INTO @Table,
@TableID
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
DECLARE ColumnScan CURSOR FOR
SELECT Length
FROM SysColumns
WHERE ID = @TableID

OPEN ColumnScan

FETCH NEXT FROM ColumnScan INTO @ColumnLength

WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT @ColCount = @ColCount + 1
SELECT @ColSize = @ColSize + @ColumnLength
FETCH NEXT FROM ColumnScan INTO @ColumnLength
END

DEALLOCATE ColumnScan

SELECT @RowCount = MAX( Rows )
FROM SysIndexes
WHERE ID= @TableID
AND ( IndID = 0 OR IndID = 1 )

SELECT @TotalColumns = @TotalColumns + @ColCount
SELECT @TotalRows= @TotalRows+ @RowCount
SELECT @RowSize = @RowCount * @ColSize

SELECT @RowBytes = RTRIM( CONVERT( char( 30 ), ROUND( ( @RowSize ), 0 ) ) )
IF ( PATINDEX( '%.%', @RowBytes ) > 0 )
BEGIN
SELECT @RowBytes = RTRIM( SUBSTRING( @RowBytes, 1, ( PATINDEX( '%.%', @RowBytes ) - 1 ) ) )
END
ELSE
BEGIN
SELECT @RowBytes = RTRIM( SUBSTRING( @RowBytes, 1, DATALENGTH( @RowBytes ) ) )
END
SELECT @TempData = RTRIM( @RowBytes ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @TotalColSize = @TotalColSize + @ColSize
SELECT @TotalDBSize = @TotalDBSize + @RowSize
SELECT @TotalTables = @TotalTables + 1
SELECT @Output = '| '
+ RTRIM( @Table )
+ SUBSTRING( @Padding, 1, ( 29 - DATALENGTH( RTRIM( @Table ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @ColCount ) )
+ SUBSTRING( @Padding, 1, ( 8 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @ColCount ) ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @ColSize ) )
+ SUBSTRING( @Padding, 1, ( 6 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @ColSize ) ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @RowCount ) )
+ SUBSTRING( @Padding, 1, ( 8 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @RowCount ) ) ) ) )
+ '| '
+ RTRIM( @TempStr )
+ SUBSTRING( @Padding, 1, ( 17 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

SELECT @ColumnLength = 0,
@ColCount = 0,
@ColSize = 0,
@RowCount = 0,
@RowSize = 0

FETCH NEXT FROM TableScan INTO @Table,
@TableID
END

DEALLOCATE TableScan

PRINT '==============================================================================='
PRINT '[ Totals ]'
PRINT '==============================================================================='

SELECT @TempData = RTRIM( CONVERT( char( 8 ), @TotalTables ) ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @Output = '[ Tables ] : '
+ RTRIM( @TempStr )
+ SUBSTRING( @Padding, 1, ( 60 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

SELECT @TempData = RTRIM( CONVERT( char( 8 ), @TotalColumns ) ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @Output = '[ Columns ] : '
+ RTRIM( @TempStr )
+ SUBSTRING( @Padding, 1, ( 60 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

SELECT @TempData = RTRIM( CONVERT( char( 8 ), @TotalColSize ) ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @Output = '[ Column Size ] : '
+ RTRIM( @TempStr )
+ ' bytes'
+ SUBSTRING( @Padding, 1, ( 54 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

SELECT @TempData = RTRIM( CONVERT( char( 8 ), @TotalRows ) ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @Output = '[ Data Rows ] : '
+ RTRIM( @TempStr )
+ SUBSTRING( @Padding, 1, ( 60 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

SELECT @TotalKB = RTRIM( CONVERT( char( 20 ), ROUND( ( @TotalDBSize / 1024 ), 0 ) ) )
SELECT @TempData = RTRIM( SUBSTRING( @TotalKB, 1, ( PATINDEX( '%.%', @TotalKB ) - 1 ) ) ),
@TempStr = ''
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
SELECT @Done = 0
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( @TempData )
SELECT @Done = 1
END
WHILE ( @Done = 0 )
BEGIN
SELECT @TempStr = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @TempStr )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @TempStr = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @TempStr )
SELECT @Done = 1
END
END
SELECT @Output = '[ Total Size ] : '
+ RTRIM( @TempStr )
+ ' KB'
+ SUBSTRING( @Padding, 1, ( 57 - DATALENGTH( RTRIM( @TempStr ) ) ) )
+ '|'
PRINT @Output

PRINT '==============================================================================='

SET NOCOUNT OFF
Ashley L Rickards
SQL DBA

 

[AshleyRickards]

Here's a sample of the results. It might not display properly.


===============================================================================
[ Table Size Report for MoneyStore as of Mar 20 2002 4:05PM ]
===============================================================================
| Table | Columns | Size | Rows | Bytes |
-------------------------------------------------------------------------------
| address | 29 | 358 | 110864 | 39,689,312 |
| AUDIT_LOG | 11 | 7927 | 0 | 0 |
| control | 2 | 29 | 3 | 87 |
| dtproperties | 7 | 857 | 0 | 0 |
| lookup | 4 | 73 | 55432 | 4,046,536 |
| mailings | 6 | 79 | 0 | 0 |
| name | 38 | 437 | 55432 | 24,223,784 |
| PASSWORD | 2 | 9 | 5 | 45 |
| reasoncd | 2 | 43 | 0 | 0 |
| sourcecd | 2 | 45 | 4 | 180 |
| statuscd | 3 | 51 | 3 | 153 |
| tblIDENTITY_SAVE | 1 | 4 | 0 | 0 |
| USERS | 1 | 12 | 0 | 0 |
===============================================================================
[ Totals ]
===============================================================================
[ Tables ] : 13 |
[ Columns ] : 108 |
[ Column Size ] : 9,924 bytes |
[ Data Rows ] : 221,743 |
[ Total Size ] : 66,367 KB |
===============================================================================
Ashley L Rickards
SQL DBA

 

[Joiner]

if you're just looking for a visual diagram, you can use the diagram tool in enterprise manager. there is a wizard to help you with the process.