Print Schema of Current Database
-- Declare variables used in script
DECLARE
-- fields
@table VARCHAR(255),
@table_id INT,
@column VARCHAR(255),
@column_type VARCHAR(255),
@column_length VARCHAR(255),
-- variables
@field_size INT = 30,
@header_size INT = 50;
-- Loop over tables
DECLARE
qryTables
CURSOR FOR
SELECT
name,
id
FROM
sysobjects o
WHERE
xtype = 'U'
ORDER BY
name
OPEN qryTables
FETCH NEXT FROM qryTables INTO @table, @table_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print Table Header
PRINT ''
PRINT replicate('-', @header_size)
PRINT '| Table: ' + @table + replicate(' ', @header_size-len(@table)-11) + '|'
PRINT replicate('-', @header_size)
PRINT ''
-- Loop Over columns
DECLARE
qryColumns
CURSOR FOR
SELECT
c.name col,
t.name type,
c.length
FROM
syscolumns c
JOIN
systypes t
ON
c.xtype = t.xtype
WHERE
id = @table_id
ORDER BY
colorder
OPEN qryColumns
FETCH NEXT FROM qryColumns INTO @column, @column_type, @column_length
-- Print Columns Header
PRINT replicate('-', @field_size*4+5)
PRINT
'||' + ' Column' + replicate(' ', @field_size-len('Column')-2) +
'||' + ' Type' + replicate(' ', @field_size-len('Type')-2) +
'||' + ' Length' + replicate(' ', @field_size-len('Length')-2) +
'||' + ' Description' + replicate(' ', @field_size-len('Description')-3) +
'||'
PRINT replicate('-', @field_size*4+5)
-- Print Columns
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'| ' + @column + replicate(' ', @field_size-len(@column)-1) +
'| ' + @column_type + replicate(' ', @field_size-len(@column_type)-1) +
'| ' + @column_length + replicate(' ', @field_size-len(@column_length)-1) +
'| ' + replicate(' ', @field_size-1) + '|'
FETCH NEXT FROM qryColumns INTO @column, @column_type, @column_length
END
CLOSE qryColumns
DEALLOCATE qryColumns
-- Print Columns Footer
PRINT replicate('-', @field_size*4+5)
FETCH NEXT FROM qryTables INTO @table, @table_id
END
CLOSE qryTables
DEALLOCATE qryTables