4

I'm using a 'MS_Description' extended property to document parameters in a stored procedure. This parameter's documentation requires more structure than just plain text. That is I would like to describe: "if value = x, then action A is taken; if value = y, then action B is taken, ...".

What is the best way to store this structure?

The following approaches occured to me:

  • Use html within the 'MS_Description', e.g.

    EXEC sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'
            Selects the foobar.  Can have the following values: 
            <dl>
                 <dt>x</dt><dd>does A</dd> 
                 <dt>y</dt><dd>does B</dd></dl>'
        @level0type = N'SCHEMA',      @level0name = [dbo],
        @level1type = N'PROCEDURE',   @level1name = N'SE_DemoProc'
        @level2type = N'PARAMETER',   @level2name = N'DemoPar';
    
    • Would developpers DBAs automatically parse html in their heads?
  • Use visual studio xml document tags the extendedproperty, @value would become:

    @value = N'
            Selects the foobar.  Can have the following values: 
            <list type="bullet">
                 <item><term>x</term><description>does A</description></item>
                 <item><term>y</term><description>does B</description></item>
            </list>'
    
    • Does SSMS, visual studio, and/or other tools interpret Xml document tags in MS_Description extended properties?

    • Does extra semantics of '', '', and '' improve people's understanding more than the html?

  • Use a different extended property to document the values that a parameter excepts?

    • Which property is commonly used for this?
    • How to encode these multiple values?

      As far as I know, an extended property can only occur once for a given (schema, procedure, parameter)-combination.

  • Using newlines, tabs, and '*' to format the plain text will not work since whitespace is collapsed from sql scripts.

    • does escaping newlines and tabs work?
  • Perhaps, I should avoid the mode parameter altogether and split this stored procedure into several distinct precedures. (But that would be a different question and out of scope for this one.)

  • 2
    Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway). – Aaron Bertrand Dec 4 '14 at 12:28
  • @Aaron Bertrand, good point. Several sources (e.g. dba.stackexchange.com/a/518/53394, technet.microsoft.com/en-us/library/ms190243.aspx, blogs.lessthandot.com/index.php/datamgmt/datadesign/…) mentioned using extended properties to document the database, therefore I assumed using them to document stored procedures was the prefered way as well. I assumed that block comments would not be stored in the database. – Kasper van den Berg Dec 4 '14 at 12:44
4

Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway).

A block comment in a view, function, trigger or stored procedure is definitely stored in the database. Everything between the two GO batch separators is stored as the body (though I confess I don't know which visual editors might strip them, since I just use a standard query editor):

GO

/*
  this is a comment
*/

CREATE PROCEDURE dbo.whatever
  /* @A is for apple, @J is for jacks */
  @A int,
  @J int
AS
BEGIN
  SET NOCOUNT ON;
  -- do stuff;
END
GO

(I will also confess that comments stored before CREATE PROCEDURE or after the final END may be prone to inadvertent exclusion when someone modifies the procedure, so it may be safer to keep the comments inside the body.)

A little trickier to store documentation for tables and columns inside the database without using extended properties. But again, I'd argue these should be documented externally anyway. If you're going to bother writing documentation for these things, you may as well put it somewhere that will still exist if the database goes south.

  • Do tools (visual studio, SSMS, etc.) render Xml document tags or html from within these comments? I will experiment and find out. – Kasper van den Berg Dec 4 '14 at 13:10
  • I don't think so - why would you want to do that? These are comments meant for humans to read, or no? – Aaron Bertrand Dec 4 '14 at 13:18
  • Human work with tools such as visual studio and SSMS to call the stored procedure, it would be convenient if these tools would show the formatted comments for example as tooltips. – Kasper van den Berg Dec 4 '14 at 13:24
  • It appears that both visual studio and SSMS do not render xml document tags in code completion tool tips. Plain text formatting with newlines, tabs, etc. does work. – Kasper van den Berg Dec 4 '14 at 13:25
  • Tools such as SSDT and SQL Prompt do actually use MS_Description. – Jonathan Allen Feb 19 '18 at 20:39
2

To my knowledge, none of the tooling that understands MS_Description also understands XML or Markdown.

I would recommend using Markdown anyways because even unformatted it is quite readable.

0

Stick with plain text when using MS_Description. A number of database design tools, including erwin Data Modeler, read and write MS_Description. Additionally, I've written some stored procedures that can be used with reports in SQL Server Reporting Services to produce a living data dictionary from the text in MS_Description.

Below is the code for the stored procedures I wrote that work with views, tables, indexes, and foreign keys. It wouldn't be hard to do something similar for Stored Procedures and Functions.

USE [master]
GO

IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYDOMAIN\sqlschemareports')
BEGIN
    PRINT 'Creating MYDOMAIN\sqlschemareports login...'
    CREATE LOGIN [MYDOMAIN\sqlschemareports] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
END

IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MYDOMAIN\sqlschemareports')
BEGIN
    PRINT 'Creating MYDOMAIN\sqlschemareports user in master...'
    CREATE USER [MYDOMAIN\sqlschemareports] FOR LOGIN [MYDOMAIN\sqlschemareports] WITH DEFAULT_SCHEMA=[dbo];
END

IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_ddlviewer' AND type = 'R')
BEGIN
    PRINT 'Creating db_ddlviewer role in master...'
    CREATE ROLE [db_ddlviewer] AUTHORIZATION [dbo];
END

PRINT 'Adding MYDOMAIN\sqlschemareports to db_ddlviewer role in master...'
EXEC sp_addrolemember N'db_ddlviewer', N'MYDOMAIN\sqlschemareports'
GO


USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSchemaInfo]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Creating sp_GetSchemaInfo stored procedure...'
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:        James Coe
-- Create date: 2010-05-06
-- Description:    Gets schema and descriptive information from a database.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSchemaInfo] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #schemainfo (
        [object_id] int NOT NULL,
        [column_id] int NULL,
        [TableName] sysname NOT NULL,
        [ColumnName] sysname NULL,
        [Type] nvarchar(231) NULL,
        [Constraints] nvarchar(MAX) NULL,
        [ColumnDescription] nvarchar(MAX) NULL,
        [TableDescription] nvarchar(MAX) NULL,
        [indextypes] varchar(50) NOT NULL DEFAULT (''''),
        [foreignKeytypes] varchar(50) NOT NULL DEFAULT ('''')
    )

    -- The first query gets the table information. The second query retrieves the column information.
    -- The results are populated into a temp table. 
    INSERT INTO    #schemainfo ([object_id], column_id, TableName, ColumnName, [Type], Constraints, [ColumnDescription], [TableDescription])
    SELECT [object_id], column_id, TableName, ColumnName, [Type], Constraints, [ColumnDescription], [TableDescription]
    FROM
        (SELECT  sys.tables.[object_id]
              , NULL AS column_id
              , sys.tables.name AS TableName
              , N'''' AS ColumnName
              , N'''' AS [Type]
              , N'''' AS Constraints
              , N'''' AS ColumnDescription
              , COALESCE(CONVERT(nvarchar(MAX), tableProperties.value), N'''') AS [TableDescription]
        FROM    sys.tables
                LEFT OUTER JOIN sys.extended_properties AS tableProperties
                    ON tableProperties.major_id = sys.tables.object_id
                       AND tableProperties.minor_id = 0
                       AND tableProperties.name = N''MS_Description''
        WHERE   (sys.tables.type = N''U'')
                AND (sys.tables.is_ms_shipped = 0)
                AND (NOT (sys.tables.name = N''sysdiagrams''))
        UNION ALL
        SELECT  sys.tables.[object_id]
              , sys.columns.column_id
              , sys.tables.name AS TableName
              , sys.columns.name AS ColumnName
              , sys.types.name
                + CASE WHEN sys.types.NAME IN (N''varchar'', N''char'', N''nvarchar'', N''nchar'',
                                               N''varbinary'', N''binary'', N''datetime2'',
                                               N''time'')
                       THEN N''('' + CASE sys.columns.max_length
                                    WHEN -1 THEN N''MAX''
                                    ELSE CONVERT(VARCHAR(50), sys.columns.max_length)
                                  END + N'')''
                       WHEN sys.types.NAME IN (N''decimal'', N''numeric'')
                       THEN N''('' + CONVERT(VARCHAR(50), sys.columns.PRECISION) + N'',''
                            + CONVERT(VARCHAR(50), sys.columns.scale) + N'')''
                       ELSE N''''
                  END AS [Type]
              , CASE sys.columns.is_nullable
                  WHEN 0 THEN N''NOT NULL''
                  WHEN 1 THEN N''NULL''
                END + CASE sys.columns.is_identity
                        WHEN 0 THEN N''''
                        WHEN 1 THEN N'' IDENTITY''
                      END + COALESCE(N'' DEFAULT '' + sys.default_constraints.definition,
                                     N'''') AS Constraints
              , COALESCE(CONVERT(nvarchar(MAX), columnDescription.value), N'''') AS [ColumnDescription]
              , N'''' AS [TableDescription]
        FROM    sys.tables
                LEFT OUTER JOIN sys.columns
                    ON sys.columns.object_id = sys.tables.object_id
                INNER JOIN sys.types
                    ON sys.columns.system_type_id = sys.types.system_type_id
                       AND sys.columns.user_type_id = sys.types.user_type_id
                LEFT OUTER JOIN sys.default_constraints
                    ON sys.default_constraints.parent_object_id = sys.tables.[object_id]
                       AND sys.default_constraints.parent_column_id = sys.columns.column_id
                LEFT OUTER JOIN sys.extended_properties AS columnDescription
                    ON columnDescription.major_id = sys.tables.object_id
                       AND columnDescription.minor_id = sys.columns.column_id
                       AND columnDescription.name = N''MS_Description''
                       AND class = 1
        WHERE   (sys.tables.type = N''U'')
                AND (sys.tables.is_ms_shipped = 0)
                AND (NOT (sys.tables.name = N''sysdiagrams''))) AS tablecolumninfo
    ORDER BY TableName
          , column_id

    -- This section retrieves index and primary key information to populate the Indexes column of the result set in the
    -- temp table.
    DECLARE @object_id INT
    DECLARE @column_id INT

    DECLARE schemaInfoReader CURSOR FAST_FORWARD LOCAL
        FOR SELECT  [object_id]
                  , column_id
            FROM    #schemainfo

    OPEN schemaInfoReader

    FETCH NEXT FROM schemaInfoReader INTO @object_id, @column_id

    WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @indexType VARCHAR(7)
            DECLARE @indexTypes VARCHAR(50)

            SET @indexTypes = ''''

            DECLARE indexReader CURSOR FAST_FORWARD LOCAL
                FOR SELECT  CASE sys.indexes.is_primary_key
                              WHEN 1 THEN ''PK''
                              ELSE
                                CASE WHEN sys.indexes.is_unique = 1 AND sys.indexes.is_unique_constraint = 1 THEN ''UK''
                                  WHEN sys.indexes.is_unique = 1 THEN ''U''
                                  ELSE ''I''
                                END
                              + CASE type
                                    WHEN 3 THEN ''X'' + CONVERT(VARCHAR(6), sys.indexes.index_id - 256000)
                                    WHEN 4 THEN ''S'' + CONVERT(VARCHAR(6), sys.indexes.index_id - 384000)
                                    ELSE CONVERT(VARCHAR(6), sys.indexes.index_id)
                                END
                              + CASE is_included_column
                                    WHEN 1 THEN ''+''
                                    ELSE ''''
                                END
                             END AS indextype
                    FROM    sys.indexes
                            INNER JOIN sys.index_columns
                                ON sys.indexes.[object_id] = sys.index_columns.[object_id]
                                   AND sys.indexes.index_id = sys.index_columns.index_id
                    WHERE   sys.indexes.[object_id] = @object_id
                            AND sys.index_columns.column_id = @column_id
                    ORDER BY sys.indexes.[object_id]
                          , sys.indexes.index_id

            OPEN indexReader

            FETCH NEXT FROM indexReader INTO @indexType

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF (LEN(@indexTypes) > 0) 
                        BEGIN
                            SET @indexTypes = @indexTypes + '', '' + @indexType
                        END
                    ELSE 
                        BEGIN
                            SET @indexTypes = @indexType
                        END

                    FETCH NEXT FROM indexReader INTO @indexType
                END

            CLOSE indexReader
            DEALLOCATE indexReader

            UPDATE  #schemainfo
            SET     indextypes = @indexTypes
            WHERE   [object_id] = @object_id
                    AND column_id = @column_id

            FETCH NEXT FROM schemaInfoReader INTO @object_id, @column_id    
        END

    CLOSE schemaInfoReader
    DEALLOCATE schemaInfoReader

    -- This section retrieves foreignKey and primary key information to populate the foreignkeys column of the result set in the
    -- temp table.
    DECLARE schemaInfoReader CURSOR FAST_FORWARD LOCAL
        FOR SELECT  [object_id]
                  , column_id
            FROM    #schemainfo

    OPEN schemaInfoReader

    FETCH NEXT FROM schemaInfoReader INTO @object_id, @column_id

    WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @foreignKeyType VARCHAR(7)
            DECLARE @foreignKeyTypes VARCHAR(50)

            SET @foreignKeyTypes = ''''

             DECLARE foreignKeyReader CURSOR FAST_FORWARD LOCAL
                FOR 
                    SELECT     ''FK'' + CONVERT(varchar(10), foreignkeys.keyid) As foreignKeyType
                    FROM         (SELECT     ROW_NUMBER() OVER (ORDER BY sys.foreign_keys.name) AS keyid
                                           , name, object_id
                                           , parent_object_id
                                           , referenced_object_id
                                  FROM          sys.foreign_keys) AS foreignkeys
                                    INNER JOIN sys.foreign_key_columns
                                        ON foreignkeys.object_id = sys.foreign_key_columns.constraint_object_id
                                             AND foreignkeys.parent_object_id = sys.foreign_key_columns.parent_object_id
                                             AND foreignkeys.referenced_object_id = sys.foreign_key_columns.referenced_object_id
                    WHERE     (sys.foreign_key_columns.constraint_object_id = @object_id)
                          AND (sys.foreign_key_columns.constraint_column_id = @column_id)
                          OR  (foreignkeys.parent_object_id = @object_id)
                          AND (sys.foreign_key_columns.parent_column_id = @column_id)
                          OR  (foreignkeys.referenced_object_id = @object_id)
                          AND (sys.foreign_key_columns.referenced_column_id = @column_id)

            OPEN foreignKeyReader

            FETCH NEXT FROM foreignKeyReader INTO @foreignKeyType

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF (LEN(@foreignKeyTypes) > 0) 
                        BEGIN
                            SET @foreignKeyTypes = @foreignKeyTypes + '', '' + @foreignKeyType
                        END
                    ELSE 
                        BEGIN
                            SET @foreignKeyTypes = @foreignKeyType
                        END

                    FETCH NEXT FROM foreignKeyReader INTO @foreignKeyType
                END

            CLOSE foreignKeyReader
            DEALLOCATE foreignKeyReader

            UPDATE  #schemainfo
            SET     foreignKeytypes = @foreignKeyTypes
            WHERE   [object_id] = @object_id
                    AND column_id = @column_id

            FETCH NEXT FROM schemaInfoReader INTO @object_id, @column_id    
        END

    CLOSE schemaInfoReader
    DEALLOCATE schemaInfoReader

    -- Ouputs the result set from the temp table.
    SELECT  indextypes AS [Indexes]
          , foreignKeytypes AS [Foreign Keys]
          , TableName AS [Table Name]
          , column_id AS [Column Number]
          , ColumnName AS [Column Name]
          , [Type]
          , Constraints
          , [ColumnDescription] AS [Column Description]
          , [TableDescription] AS [Table Description]
    FROM    #schemainfo

    DROP TABLE #schemainfo
END
'
END
GO

EXEC sys.sp_MS_marksystemobject sp_GetSchemaInfo
GO

GRANT EXECUTE ON [sp_GetSchemaInfo] TO [db_ddlviewer] AS [dbo]
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetForeignKeyInfo]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Creating sp_GetForeignKeyInfo stored procedure...'

EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:        James Coe
-- Create date: 2013-03-05
-- Description:    Gets the foreign key information for a database.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetForeignKeyInfo] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT     ''FK'' + CONVERT(varchar(10), foreignkeys.keyid) AS FKNumber
              , foreignkeys.name AS ForeignKeyName
              , OBJECT_NAME(foreignkeys.parent_object_id) AS ForeignKeyTable
              , foreignkeycolumns.name AS ForeignKeyColumn
              , OBJECT_NAME(foreignkeys.referenced_object_id) AS ReferencedTable
              , referencedcolumns.name AS ReferencedColumnName
    FROM         (SELECT     ROW_NUMBER() OVER (ORDER BY name) AS keyid
                           , name
                           , object_id, parent_object_id
                           , referenced_object_id
                  FROM          sys.foreign_keys) AS foreignkeys
                 INNER JOIN sys.foreign_key_columns
                     ON     foreignkeys.object_id = sys.foreign_key_columns.constraint_object_id
                        AND foreignkeys.parent_object_id = sys.foreign_key_columns.parent_object_id
                        AND foreignkeys.referenced_object_id = sys.foreign_key_columns.referenced_object_id
                 INNER JOIN sys.columns AS foreignkeycolumns
                     ON     sys.foreign_key_columns.parent_object_id = foreignkeycolumns.object_id
                        AND sys.foreign_key_columns.parent_column_id = foreignkeycolumns.column_id
                 INNER JOIN sys.columns AS referencedcolumns
                     ON     sys.foreign_key_columns.referenced_object_id = referencedcolumns.object_id
                        AND sys.foreign_key_columns.referenced_column_id = referencedcolumns.column_id
    ORDER BY foreignkeys.keyid, foreign_key_columns.parent_column_id
END
'
END
GO

EXEC sys.sp_MS_marksystemobject sp_GetForeignKeyInfo
GO

GRANT EXECUTE ON [sp_GetForeignKeyInfo] TO [db_ddlviewer] AS [dbo]
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetIndexInfo]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Creating sp_GetIndexInfo stored procedure...'

EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:        James Coe
-- Create date: 2013-03-06
-- Description:    Gets the index information for a database.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetIndexInfo]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT     CASE sys.indexes.is_primary_key
                  WHEN 1 THEN ''PK''
                  ELSE
                    CASE WHEN sys.indexes.is_unique = 1 AND sys.indexes.is_unique_constraint = 1 THEN ''UK''
                         WHEN sys.indexes.is_unique = 1 THEN ''U''
                         ELSE ''I''
                    END
                  + CASE sys.indexes.type
                        WHEN 3 THEN ''X'' + CONVERT(VARCHAR(6), sys.indexes.index_id - 256000)
                        WHEN 4 THEN ''S'' + CONVERT(VARCHAR(6), sys.indexes.index_id - 384000)
                        ELSE CONVERT(VARCHAR(6), sys.indexes.index_id)
                    END
                 END AS IndexNumber
             , OBJECT_NAME(sys.indexes.object_id) AS TableName
             , sys.indexes.name AS IndexName
             , sys.indexes.type_desc
             , sys.indexes.is_primary_key
             , sys.indexes.fill_factor
             , sys.indexes.is_disabled
             , sys.columns.name AS ColumnName
             , sys.index_columns.is_included_column
             , sys.index_columns.is_descending_key
    FROM         sys.indexes
                 INNER JOIN sys.index_columns
                     ON     sys.indexes.object_id = sys.index_columns.object_id
                        AND sys.indexes.index_id = sys.index_columns.index_id
                 INNER JOIN sys.columns
                     ON     sys.index_columns.object_id = sys.columns.object_id
                        AND sys.index_columns.column_id = sys.columns.column_id
    WHERE      (EXISTS ( SELECT 1 FROM sys.tables WHERE sys.tables.object_id = sys.indexes.object_id AND NOT sys.tables.name = N''sysdiagrams'' ))
    ORDER BY TableName, ~sys.indexes.is_primary_key, sys.indexes.name, sys.index_columns.index_column_id, sys.index_columns.key_ordinal
END
'
END
GO

EXEC sys.sp_MS_marksystemobject sp_GetIndexInfo
GO

GRANT EXECUTE ON [sp_GetIndexInfo] TO [db_ddlviewer] AS [dbo]
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetViewInfo]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Creating sp_GetViewInfo stored procedure...'

EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:        James Coe
-- Create date: 2013-04-10
-- Description:    Gets the names of the views and the columns in each view.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetViewInfo] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT [object_id], ColumnNumber, ViewName, ColumnName, [Type], Constraints, [ColumnDescription], [ViewDescription]
    FROM
        (SELECT  sys.views.[object_id]
              , NULL AS ColumnNumber
              , sys.views.name AS ViewName
              , N'''' AS ColumnName
              , N'''' AS [Type]
              , N'''' AS Constraints
              , N'''' AS ColumnDescription
              , COALESCE(CONVERT(nvarchar(MAX), viewProperties.value), N'''') AS [ViewDescription]
        FROM    sys.views
                LEFT OUTER JOIN sys.extended_properties AS viewProperties
                    ON viewProperties.major_id = sys.views.object_id
                       AND viewProperties.minor_id = 0
                       AND viewProperties.name = N''MS_Description''
        WHERE   (sys.views.type = N''V'')
                AND (sys.views.is_ms_shipped = 0)
        UNION ALL
        SELECT  sys.views.[object_id]
              , sys.columns.column_id AS ColumnNumber
              , sys.views.name AS ViewName
              , sys.columns.name AS ColumnName
              , sys.types.name
                + CASE WHEN sys.types.NAME IN (N''varchar'', N''char'', N''nvarchar'', N''nchar'',
                                               N''varbinary'', N''binary'', N''datetime2'',
                                               N''time'')
                       THEN N''('' + CASE sys.columns.max_length
                                    WHEN -1 THEN N''MAX''
                                    ELSE CONVERT(VARCHAR(50), sys.columns.max_length)
                                  END + '')''
                       WHEN sys.types.NAME IN (N''decimal'', N''numeric'')
                       THEN N''('' + CONVERT(VARCHAR(50), sys.columns.PRECISION) + N'',''
                            + CONVERT(VARCHAR(50), sys.columns.scale) + N'')''
                       ELSE N''''
                  END AS [Type]
              , CASE sys.columns.is_nullable
                  WHEN 0 THEN N''NOT NULL''
                  WHEN 1 THEN N''NULL''
                END + CASE sys.columns.is_identity
                        WHEN 0 THEN N''''
                        WHEN 1 THEN N'' IDENTITY''
                      END + COALESCE(N'' DEFAULT '' + sys.default_constraints.definition,
                                     N'''') AS Constraints
              , COALESCE(CONVERT(nvarchar(MAX), columnDescription.value), N'''') AS [ColumnDescription]
              , N'''' AS [ViewDescription]
        FROM    sys.views
                LEFT OUTER JOIN sys.columns
                    ON sys.columns.object_id = sys.views.object_id
                INNER JOIN sys.types
                    ON sys.columns.system_type_id = sys.types.system_type_id
                       AND sys.columns.user_type_id = sys.types.user_type_id
                LEFT OUTER JOIN sys.default_constraints
                    ON sys.default_constraints.parent_object_id = sys.views.[object_id]
                       AND sys.default_constraints.parent_column_id = sys.columns.column_id
                LEFT OUTER JOIN sys.extended_properties AS columnDescription
                    ON columnDescription.major_id = sys.views.object_id
                       AND columnDescription.minor_id = sys.columns.column_id
                       AND columnDescription.name = N''MS_Description''
                       AND class = 1
        WHERE   (sys.views.type = N''V'')
                AND (sys.views.is_ms_shipped = 0)) AS viewcolumninfo
    ORDER BY ViewName
          , ColumnNumber
END
'
END
GO

EXEC sys.sp_MS_marksystemobject sp_GetViewInfo
GO

GRANT EXECUTE ON [sp_GetViewInfo] TO [db_ddlviewer] AS [dbo]
GO

DECLARE @databaseName sysname
DECLARE @sql nvarchar(MAX)

DECLARE databaseNames CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(name) FROM sys.databases WHERE database_id > 4 OR name = N'model'

OPEN databaseNames

FETCH NEXT FROM databaseNames INTO @databaseName

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT N'Adding db_ddlviewer role to ' + @databasename + '...'
    SET @sql = N'USE ' + @databaseName + N'; IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''db_ddlviewer'' AND type = ''R'') CREATE ROLE [db_ddlviewer] AUTHORIZATION [dbo]; GRANT VIEW DEFINITION TO [db_ddlviewer]'
    EXEC sp_executesql @sql
    FETCH NEXT FROM databaseNames INTO @databaseName
END

CLOSE databaseNames
DEALLOCATE databaseNames
GO

DECLARE @databaseName sysname
DECLARE @sql nvarchar(MAX)

DECLARE databaseNames CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(name) FROM sys.databases WHERE database_id > 4 OR name = N'model'

OPEN databaseNames

FETCH NEXT FROM databaseNames INTO @databaseName

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT N'Adding MYDOMAIN\sqlschemareports user to ' + @databasename + '...'
    SET @sql = 'USE ' + @databaseName + '; IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''MYDOMAIN\sqlschemareports'') CREATE USER [MYDOMAIN\sqlschemareports] FOR LOGIN [MYDOMAIN\sqlschemareports] WITH DEFAULT_SCHEMA=[dbo]; EXEC sp_addrolemember N''db_ddlviewer'', N''MYDOMAIN\sqlschemareports'''
    EXEC sp_executesql @sql
    FETCH NEXT FROM databaseNames INTO @databaseName
END

CLOSE databaseNames
DEALLOCATE databaseNames
GO

In the output of the stored procedures the following identifiers are used:

  • PK - Primary Key
  • FK - Foreign Key
  • I - Index
  • U - Unique Index
  • UK - Unique Key
  • IX - XML Index
  • IS - Spatial Index
  • + - Included Column (in an Index or Unique Index)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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