system_function_schema.fn_datadictionary

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:19:01 GMT
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.

Oskar Austegard
http://mo.notono.us


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
DZone Snippets: datadictionary code

system_function_schema.fn_datadictionary
