SQL: DataDictionary System Function

The following lengthy SQL script will create a system function in SQL Server that returns a data dictionary for any database on the server when called like this:

select * from ::fn_datadictionary()

The output includes the Description of the objects.

More info on creating system functions can be found in the Dec 2001 edition of SQL Server Professional.

Now, for the SQL:

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE master
go
CREATE FUNCTION system_function_schema.fn_datadictionary ()
RETURNS @DataDictionary TABLE (
 object_id int,
 object_name nvarchar(128),
 object_type nvarchar(128),
 column_order int NULL,
 column_name varchar(60) NULL,
 column_datatype varchar(20) NULL,
 column_length int NULL,
 object_description varchar(500) NULL
)
AS
BEGIN

 DECLARE @object_name nvarchar(128)
-------------------------
--Tables and Columns
-------------------------
 DECLARE table_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('U') AND status > 1 ORDER BY name

 OPEN table_cursor
 FETCH NEXT FROM table_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Tables
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Columns
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table Column',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, N'column', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
  ORDER BY c.colorder

    FETCH NEXT FROM table_cursor INTO @object_name
 END
 CLOSE table_cursor
 DEALLOCATE table_cursor

-------------------------
--Views and Columns
-------------------------
 DECLARE view_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('V') AND status > 1 ORDER BY name

 OPEN view_cursor
 FETCH NEXT FROM view_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Views
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Columns
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View Column',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, N'column', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
  ORDER BY c.colorder

    FETCH NEXT FROM view_cursor INTO @object_name
 END
 CLOSE view_cursor
 DEALLOCATE view_cursor


---------------------------
--Procedures and Parameters
---------------------------
 DECLARE proc_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('P') AND status > 1 ORDER BY name

 OPEN proc_cursor
 FETCH NEXT FROM proc_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Procedures
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Parameters
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure Parameter',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, N'parameter', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
   AND t.name <> 'sysname'
  ORDER BY c.colorder

    FETCH NEXT FROM proc_cursor INTO @object_name
 END
 CLOSE proc_cursor
 DEALLOCATE proc_cursor



---------------------------
--Functions and Parameters
---------------------------
 DECLARE func_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('FN', 'TF', 'IF') AND status > 1 ORDER BY name

 OPEN func_cursor
 FETCH NEXT FROM func_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Functions
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Parameters
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function Parameter',
    column_order = c.colorder, 
   column_name = CASE WHEN c.[name] = '' THEN '' ELSE c.[name] END, 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, N'parameter', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
   AND t.name <> 'sysname'
  ORDER BY c.colorder

    FETCH NEXT FROM func_cursor INTO @object_name
 END
 CLOSE func_cursor
 DEALLOCATE func_cursor
 RETURN
END
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO