Post a Comment On: mo.notono.us

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