Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: How detailed are your table and column descriptions in SSMS?

    I was just curious what best practice is for documenting tables and columns.....

    I've been task with a very large data warehouse project and the first piece consist of documenting all the tables and columns. I drafted up a system query that pulls back a lot of the attribute information that is required, now I am on documenting the tables and columns and I was wondering how detailed should you go?

    Thanks for any follow ups.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would STRONGLY recommend that you consider using SQL Spec. There is a free version that you can download and try before you buy. It does so many things that I'd be wasting your time and mine to list them here... Just try it, and I'm pretty sure that you'll buy it (and it is very reasonably priced too).

    There are many other tools that will help with database documentation, but the litmus test for when you're "done" with documentation is when you can comfortably hand over the documents to the new kid that shows up, ask them a moderately complicated question, and know that they can get the answer from the documentation (without needing to access the database).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    First of all thanks for suggesting a solution.

    As far as the documentation goes this sounds like a year long project. 300 tables/views make it complicated to explain everything and having to interview people to elicit the information can be challenging when they are worried about their job function.

    Either way this is a daunting project and I was just looking for some guidance. Thanks

    Pat do you know of any white papers or examples of how detailed you should document (Table/Column) descriptions in MS SQL Server Extended Properties? That would be helpful to see some actual descriptions in the database.

    I'm trying to get buy in from my managers to set up some governance over table creation and when a table is created it needs to have table and column descriptions. That way I can run this query.

    SELECT schemas.name AS SchemaName
    ,all_objects.name AS TableName
    --,syscolumns.id AS ColumnId
    ,syscolumns.name AS ColumnName
    ,syscolumns.colid AS ColumnID
    ,syscomments.TEXT AS ColumnDefault
    ,sys.all_objects.type AS [Type]
    ,sys.all_objects.type_desc As [Type_Desc]
    ,CASE WHEN syscolumns.isnullable = 0 THEN 'nulls not allowed' ELSE 'nulls allowed' END AS NULLSTATUS
    --,syscolumns.isnullable AS IsNullable
    ,systypes.name AS DataType
    ,syscolumns.length AS CharacterMaximumLength
    ,Table_Properties.[value] AS [Table_Description]
    ,Column_Properties.[value] AS [Column_Description] ---- Column Description
    FROM syscolumns
    INNER JOIN sys.systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sys.all_objects
    ON syscolumns.id = all_objects.[object_id]
    LEFT OUTER JOIN sys.extended_properties AS Column_Properties
    ON (Column_Properties .minor_id = syscolumns.colid AND Column_Properties.major_id = syscolumns.id)
    LEFT OUTER JOIN sys.extended_properties AS Table_Properties
    ON (Table_Properties .minor_id = 0 AND Table_Properties.major_id = syscolumns.id)
    LEFT OUTER JOIN sys.syscomments
    ON syscolumns.cdefault = syscomments.id
    LEFT OUTER JOIN sys.schemas
    ON schemas.[schema_id] = all_objects.[schema_id]

    WHERE syscolumns.id IN (SELECT id
    FROM sysobjects
    WHERE xtype = 'U' or xtype = 'v')
    AND (systypes.name <> 'sysname')
    AND (sys.all_objects.is_ms_shipped = '0')
    ORDER BY type_desc, TableName, syscolumns.colid
    And be able to dynamically always report on what is in the database and what the tables and column defs are.
    Last edited by VLOOKUP; 02-19-15 at 11:51.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be very surprised if you can't document the technical side of the databases in an afternoon. As you pointed out, the SME (Subject Matter Expert) perspective takes a lot more work and much more time.

    With that said, if you can produce the technical documentation first, that makes talking to the SMEs a lot easier. Once you get even one of the SMEs to help you document the schema (which SQL-Spec will gladly incorporate into your documentation), your users will persuade the remaining SMEs to help you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I can document the technical side, what I can't get done is documenting all the descriptions for the tables and columns from a business perspective, I don't know what half of them are.

    I am spending a few weeks faciliating a whiteboard meeting to pin ownership of these tables on the parties then follow up with documentation.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat just an FYI - The tables have already been created, I am faciliating a meeting to get the descriptions captured, there are about 5 different people I'll have to interview and work with to get this information.

    My concern is I am going to get to deep or to shallow with my definitions.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I feel like I'm either asking or answering the wrong questions. Have you looked at SQL-Spec (specifically the demo output)? If you haven't seen that to understand the context, what I'm saying probably doesn't make any sense.

    You can document until your fingers bleed. You can talk about documentation as an abstract thing until you run out of breath or die of starvation. SMEs grok the physical, things that they can see. The output of SQL-Spec gives them that "solid frame of reference" that makes creating the kind of documentation that I normally want much easier because they can visualize the tables, views, procedures, functions, etc. and use a familiar user interface to understand what those things are and how they relate to each other... Once I can get the SME to the point that they "feel" the objects, I find it far easier to discuss and get them to provide the business details that I really want to capture.

    Once I get one of the SMEs to provide that business information and I update the documentation, the users will lobby the SMEs to provide the information that I'm trying to capture and the remaining SMEs can also see how providing that information will help them and the business. Once I get past the first SME providing the business documentation, it seems like having a dam break... My biggest problem at that point is how fast can I type!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I've looked at the utility it looks pretty nice. I sent the link over to our .net developer to take a look and give me his thoughts.

    Thanks for sharing that was cool of you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •