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)
