0

I wrote the following (Quick and Dirty) script to document a database schema: Note - The relationships part came from another guys site (I just can't remember where). Does anyone know how to integrate the Column Descriptions and Table Description:

Set NoCount On

--Cleanup 
If Object_Id('TempDb..##Database_Tables') Is Not Null Begin Drop Table ##Database_Tables End

--Create temp tables 
Create Table ##Database_Tables (Table_Id Int Identity(1,1) Primary Key, Table_Name Varchar(255));

--Create variables 
Declare @Database_Name Varchar(Max) = '';
Declare @Document_Title Varchar(Max) = '';
Declare @Document_Start Varchar(Max) = '';
Declare @Document_Finish Varchar(255) = '';
Declare @Document_Styles Varchar(Max) = '';
Declare @Document_Body Varchar(Max) = '';

--Create basic document data 
Set @Database_Name = 'master';
Set @Document_Title = @Database_Name + ' Database Documentation';
Set @Document_Start = '<html><head><style type="text/css">@Document_Styles</style></head><body>'; 
Set @Document_Styles = '*{margin:0px;padding:0px;border-collapse:collapse;font-family:tahoma;font-size:12px;}' + 
                       '.title{position:relative;top:0px;left:0px;font-weight:bold;color:black;background-color:#eeeeee;margin-bottom:10px;font-size:20px;}' + 
                       '.section{position:relative;top:0px;left:0px;width:800px;border:1px solid silver;margin-bottom:10px;padding:10px!important;}' + 
                       '.section .header{position:relative;top:0px;left:0px;width:400px;height:20px;font-weight:bold;color:navy;text-decoration:underline;font-size:14px;}' + 
                       '.table{margin-bottom:5px;}' + 
                       '.table thead{background-color:gray;border:1px solid black}' + 
                       '.table th{border:1px solid black;padding-right:10px;padding-top:2px;padding-bottom:2px;}' + 
                       '.table td{border:1px solid black;padding-left:10px;padding-right:10px;padding-top:2px;padding-bottom:2px;}' + 
                       '.subsection{position:relative;top:0px;margin-left:10px;}' + 
                       '.subsection .header{color:red!important;font-size:12px!important;}';

--Create basic document data 
Exec ('Insert Into ##Database_Tables(Table_Name) ' + 
      'Select Table_Name ' + 
      'From ' + @Database_Name + '.Information_Schema.Tables ' + 
      'Order By Table_Name'
);

--Print document start and title
Print Replace(@Document_Start, '@Document_Styles', @Document_Styles);
Print '<div class="title">' + @Document_Title + '</div>';

--Document table schema
Declare @Table_Name Varchar(255) = '';
Declare @Table_Count Integer = 0;
Declare @Table_Data Table (Table_Information Varchar(Max));
Declare @Table_Id Integer = 1;
Select @Table_Count = Count(Table_Name) From ##Database_Tables;
Select @Table_Id = 1;

While @Table_Id <= @Table_Count
Begin
  Declare @TableId Integer;
  Declare @Test Varchar(Max) = '';

  Select @Table_Name = Table_Name From ##Database_Tables Where Table_Id = @Table_Id;

  --Get primary table info
  Delete From @Table_Data;
  Insert Into @Table_Data (Table_Information) 
  Select '<tr><td>' + Table_Name + '</td><td>' + Column_Name + '</td><td>' + IsNull(Replace(Character_Maximum_Length, '-1', 'Max'),'') + '</td><td>' + Data_Type  + '</td><td>' + Is_Nullable + '</td></tr>' As Table_Information
  From Information_Schema.Columns Where Table_Name = @Table_Name;

  Declare @Table_Information Varchar(Max); 
  Select @Table_Information = Coalesce(@Table_Information + '', '') + Table_Information
  From @Table_Data
  Where Table_Information Is Not Null

  Print '<div class="section">';
  Print '<div class="header">Table Name: ' + @Table_Name + '</div>';
  Print '<table class="table"><thead><th>Table Name</th><th>Column_Name</th><th>Column_Length</th><th>Data_Type</th><th>Nullable</th><thead><tbody>';
  Print @Table_Information;
  Print '</table>';
  Set @Table_Information = Null;

  --Get relationship info (Reference needed)
  Delete From @Table_Data;
  Insert Into @Table_Data (Table_Information) 
  Select '<tr><td>' + Fk.Table_Name + '</td><td>' + Cu.Column_Name + '</td><td>' + Pt.Column_Name + '</td><td>' + C.Constraint_Name+ '</td></tr>' As Table_Information
  From Information_Schema.Referential_Constraints C
  Inner Join Information_Schema.Table_Constraints Fk On C.Constraint_Name = Fk.Constraint_Name
  Inner Join Information_Schema.Table_Constraints Pk On C.Unique_Constraint_Name = Pk.Constraint_Name
  Inner Join Information_Schema.Key_Column_Usage CU On C.Constraint_Name = Cu.Constraint_Name
  Inner Join (
  Select i1.Table_Name, i2.Column_Name
  From Information_Schema.Table_Constraints i1
  Inner Join Information_Schema.Key_Column_Usage i2 On i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
  Where i1.Constraint_Type = 'Primary Key'
  ) Pt On Pt.Table_Name = Pk.Table_Name
  And Pk.Table_Name = @Table_Name

  Select @Table_Information = Coalesce(@Table_Information + '', '') + Table_Information
  From @Table_Data
  Where Table_Information Is Not Null

  Print '<div class="subsection">';
  Print '<div class="header">Foreign Relationships: </div>';
  Print '<table class="table"><thead><th>Foreign_Table</th><th>Foreign_Column_Name</th><th>Primary_Table_Key</th><th>Constraint_Name</th><thead><tbody>';
  Print @Table_Information;
  Print '</table>';

  Print '<tbody></table></div></div>';
  Set @Table_Information = Null;

  Set @Table_Id = @Table_Id + 1;
End

Print '</body></html>';
Set NoCount Off;
Improve this question
6
  • 1
    Huh? Are you talking about extended properties? Do people really use those? What have you tried?
    – Aaron Bertrand
    Commented Sep 15, 2015 at 20:25
  • @AaronBertrand Yes some of us do :)
    – LowlyDBA - John M
    Commented Sep 15, 2015 at 20:36
  • If you right click on a table (You can add comments). If click on a column you can add comments. Where are these located. Note - I published the whole script so that others may benefit from it when finished. You just copy the printout and past it into an html document.
    – Chris
    Commented Sep 15, 2015 at 20:42
  • This goes a bit outside of what you are asking but since I saw this at a SQL Saturday a few weeks ago, I figured I would share. It is easily the best tool for documenting a database (and the server, and everything else around it) that I have seen. github.com/gwalkey George presented it and I have used it on a dozen servers now. It wiill not give you the descriptions you are asking for, but it pretty much does everything else.
    – mskinner
    Commented Sep 15, 2015 at 20:45
  • In any case, that is definitely not my idea of "quick and dirty" :-)
    – Aaron Bertrand
    Commented Sep 15, 2015 at 22:53

1 Answer 1

Reset to default
1

Chris, For extended properties look at sys.extended_properties.

I'm not sure where to fit that in your table, but start at

SELECT ObjectName = o.name
    , o.type_desc
    , PropertyName = xp.name
    , PropertyValue = xp.value
FROM sys.extended_properties xp
    INNER JOIN sys.objects o ON (xp.class = 1 or xp.class = 2 or xp.class = 7)
    AND xp.major_id = o.object_id;

to get an idea of what is in there.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.