if exists (select * from sysobjects where id = object_id('dbo.sp_dba_PublishData') and sysstat & 0xf = 4)
	drop procedure dbo.sp_dba_PublishData
GO

create procedure sp_dba_PublishData
as
-- EXECUTE sp_dba_PublishData
--
--			Enterprise Solutions 
--
--           File:	{}:\Technology Services\Projects\Content Management\bin\sp_dba_PublishData.SQL
--           Date:	March 27th, 2001
--         Author:	Brett Kaiser
--         Server:	
--       Database:	Any Database - Only reads sysobjects
--          Login:	sa
--    Description:	Publish a html pages for User Table Objects in the database
--    Tables Used:	SysObjects
-- Tables Created:	None
--
--			The stream will do the following:
--
--			1. Create all of the Grants from the System Catalog for tables that are Prefixed with EDG
--  Row Estimates:
--  			name                 rows        reserved           data               index_size         unused             
--			-------------------- ----------- ------------------ ------------------ ------------------ ------------------ 
--		
--
--     Change Log:
--												
--  			User 		Date	   	Description											
-- 			------- 	---------- 	-----------------------------------------------------------------------------
--  			x002548		03/27/2001 	Initial Implementation
--

declare @Name sysname
declare @sp_sql  char(256)
declare @sp_location char(256)
declare @sp_path char(256)

Select @sp_path = RTrim(N'E:\Christos\Pages\')

Declare CRSR Cursor Static For 
			Select RTrim(name) as Name from sysobjects 
			 Where xtype = 'U' and name like 'TBL%'
Open CRSR

fetch next from CRSR into @Name

While @@fetch_status = 0

Begin

	Select @sp_sql =        RTrim(N'SELECT * FROM ' 
		              + RTrim(@Name))

  	Select @sp_location =   RTrim(@sp_path) 
			      + RTrim(@Name) 
			      + '.htm'

-- sa doesn't auth to write to the location below...and sa is required to run sp_makewebtask
--	Select @sp_location =   RTrim(' 
--		              + RTrim(@Name) 
--			      + '.htm')

	Select 			'Tablename: ' 
			      + Rtrim(@Name)
			      + ' is extracted with SQL: '
			      + RTrim(@sp_sql)
			      + ' and is placed in location: ' 
			      + RTrim(@sp_location) 


	EXECUTE sp_makewebtask @outputfile = @sp_location
		, @query = @sp_sql
		, @fixedfont=1
		, @HTMLheader=3 
		, @webpagetitle=N'Microsoft SQL Server Web Assistant'
		, @resultstitle=N'Query Results'
		, @dbname=N'RealData'
		, @whentype=1
		, @procname=N'RealData Web Page'
		, @codepage=65001,@charset=N'utf-8'

	fetch next from CRSR into @Name
	End

Deallocate CRSR

-- Create Reference Page


Select @sp_path =   RTrim(N'')

Select 	  '<LI><A HREF="' 
	+  RTrim(@sp_path)
	+  RTrim(name)
	+ '.htm">'
	+  RTrim(name)
	+ '</A>' as strLink 
  from 	  sysobjects 
 Where 	  xtype = 'U' and name like 'TBL%'
Order by name

GO

SET NOCOUNT ON

CREATE TABLE #DataDictionary
(
TableName SYSNAME NOT NULL,
ColumnName SYSNAME NOT NULL,
Description VARCHAR(255) NOT NULL,
ColumnOrder INT NOT NULL
)

INSERT INTO 
	#DataDictionary
SELECT 
	o.name, c.name, CONVERT(VARCHAR(255), p.value), c.colid
FROM 
	syscolumns c, sysproperties p, sysobjects o
WHERE	
	p.smallid = c.colid AND
	c.id = p.id AND 
	c.id = o.id
UNION

SELECT 
	o.name, '', CONVERT(VARCHAR(255), p.value), 0
FROM 
	sysproperties p, sysobjects o
WHERE
	p.id = o.id AND
	p.smallid = 0
ORDER BY
	1, 4

-- This is your Data Dictionary
-- Export to Excel through DTS
SELECT 
	CONVERT(varchar(30), TableName) AS TableName, CONVERT(varchar(40), ColumnName) AS ColumnName, Description
FROM 
	#DataDictionary

DROP TABLE 
	#DataDictionary

quote:

---

The current release of this code can generate documentation for Oracle and PostgreSQL schemas

---

quote:

---

One question though, why not use INFORMATION_SCHEMA views?

---

quote:

---

Just looked at the INFORMATION_SCHEMA and there doesn't seem to be a view that gets the description, which is the value column from the sysproperties table in my code. If I could find the description in one of those views, then this could easily be rewritten, but I don't seen it in the views. The three other columns that I use are in the views but not description.

---

USE Northwind
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DICT_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DICT_TABLE]
GO

CREATE TABLE DICT_TABLE (TABLE_NAME sysname NOT NULL, Description varchar(4000))
GO

INSERT INTO DICT_TABLE (TABLE_NAME, Description)
SELECT TABLE_NAME, Convert(varchar(4000),' ') As Description FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DICT_COLUMN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DICT_COLUMN]
GO

CREATE TABLE DICT_COLUMN (TABLE_NAME sysname NOT NULL, COLUMN_NAME sysname NOT NULL, Description varchar(4000))
GO

INSERT INTO DICT_COLUMN (TABLE_NAME, COLUMN_NAME, Description)
SELECT TABLE_NAME, COLUMN_NAME, Convert(varchar(4000),' ') As Description 
FROM INFORMATION_SCHEMA.Columns o
WHERE EXISTS ( SELECT 1 FROM DICT_TABLE i WHERE o.TABLE_NAME = i.TABLE_NAME)
ORDER BY TABLE_NAME
GO


UPDATE DICT_TABLE SET Description = 'Contains a list of all types of Descriptiveclassifications that products may belong to. Contains a list of all types of Descriptiveclassifications that products may belong to. Contains a list of all types of Descriptiveclassifications that products may belong to.'
WHERE TABLE_NAME = 'Categories'
GO

UPDATE DICT_COLUMN SET Description = 'This is the key to the table and is an icremental value starting with 1'
WHERE TABLE_NAME = 'Categories' AND COLUMN_NAME = 'CategoryID'
GO

if exists (select * from sysobjects where id = object_id('dbo.isp_dba_PublishDictionary') and sysstat & 0xf = 4)
	drop procedure dbo.isp_dba_PublishDictionary
GO

CREATE PROCEDURE isp_dba_PublishDictionary @dbnameIN sysname, @sp_path varchar(256)
AS

DECLARE @TABLE_NAME sysname, @sp_sql  varchar(4000), @sp_location varchar(256), @Description varchar(4000)
	, @WebPageTitleIN varchar(80), @resultstitleIN varchar(80), @ProcNameIN varchar(255)

DECLARE CRSR CURSOR STATIC FOR 
	SELECT    l.TABLE_NAME
		, r.Description 
	  FROM    INFORMATION_SCHEMA.Tables l
     LEFT JOIN	  DICT_TABLE r
	    ON	  l.TABLE_NAME = r.TABLE_NAME 
	 WHERE 	  l.TABLE_TYPE = 'BASE TABLE' 

OPEN CRSR

FETCH NEXT FROM CRSR into @TABLE_NAME, @Description

WHILE @@fetch_status = 0 BEGIN

	SELECT @sp_sql =          
				  'SELECT l.TABLE_NAME, r.Description'
				+ ' FROM INFORMATION_SCHEMA.Tables l' 
				+ ' LEFT JOIN DICT_TABLE r'
				+ ' ON l.TABLE_NAME = r.TABLE_NAME'
				+ ' WHERE l.TABLE_NAME ='
				+ ''''
				+ RTrim(@TABLE_NAME)
				+ ''''
				+ ' '
				+ 'SELECT '
				+ ' l.COLUMN_NAME, l.DATA_TYPE, l.IS_NULLABLE, r.description'
				+ ' FROM INFORMATION_SCHEMA.Columns l' 
				+ ' LEFT JOIN DICT_COLUMN r'
				+ ' ON l.TABLE_NAME = r.TABLE_NAME'
				+ ' AND l.COLUMN_NAME = r.COLUMN_NAME'
				+ ' WHERE l.TABLE_NAME ='
				+ ''''
				+ RTrim(@TABLE_NAME)
				+ ''''
				+ ' ORDER BY ORDINAL_POSITION'
		, @sp_location = @sp_path 
			       + @TABLE_NAME 
			       + '.htm'
		, @WebPageTitleIN = 'Data Dictionary for '+ @DBNameIN
		, @resultstitleIN = Left((@TABLE_NAME + ':' + @Description), 255)
		, @procNameIN = 'wbtsk_'+@TABLE_NAME


	SELECT 			'Tablename: ' 
			      + Rtrim(@TABLE_NAME)
			      + ' is extracted with SQL: '
			      + RTrim(@sp_sql)
			      + ' and is placed in location: ' 
			      + RTrim(@sp_location) 


	EXECUTE sp_makewebtask @outputfile = @sp_location
		, @query = @sp_sql
		, @fixedfont=1
		, @HTMLheader=3 
		, @webpagetitle=@WebPageTitleIN
		, @resultstitle=@resultstitleIN
		, @dbname=@dbNameIN
		, @whentype=1
		, @procname=@procnameIN
		, @codepage=65001,@charset=N'utf-8'

	FETCH NEXT FROM CRSR INTO @TABLE_NAME, @Description
	END

CLOSE CRSR
DEALLOCATE CRSR
GO

master..xp_cmdshell 'MD C:\TEMP\DICT\'
GO

EXEC isp_dba_PublishDictionary 'Northwind','C:\TEMP\DICT\'
GO

master..xp_cmdshell 'DIR C:\TEMP\DICT\*.*'
GO

master..xp_cmdshell 'COPY C:\TEMP\DICT\*.* \\UNC filename\*.*'
GO