Raymond Lewallen

Here it is as a function returning a table (useful for queries like this:

select *
from dbo.fnDataDictionary()
where column_description is null


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fnDataDictionary ()
RETURNS @DataDictionary TABLE (
table_id int NULL,
table_name nvarchar(128) NULL,
column_order int NULL,
column_name varchar(60) NULL,
column_datatype varchar(20) NULL,
column_length int NULL,
column_description varchar(500) NULL
)
AS
BEGIN

DECLARE @table_name nvarchar(128)
DECLARE tablenames_cursor CURSOR FOR
SELECT name FROM sysobjects where type = 'U' and status > 1 order by name

OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
insert @DataDictionary
select
o.[id] as 'table_id',
o.[name] as 'table_name',
0 as 'column_order',
NULL as 'column_name',
NULL as 'column_datatype',
NULL as 'column_length',
Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, null, default) e
on o.name = e.objname
where o.name = @table_name

insert @DataDictionary
select
o.[id] as 'table_id',
o.[name] as 'table_name',
c.colorder as 'column_order',
c.[name] as 'column_name',
t.[name] as 'column_datatype',
c.[length] as 'column_length',
Cast(e.value as varchar(500)) as 'column_description'
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', @table_name, N'column', null) e
on c.name = e.objname
where o.name = @table_name
order by c.colorder

FETCH NEXT FROM tablenames_cursor INTO @table_name
END

CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Awesome Oskar, thanks for the modifications to a UDF. Since I only run it once in a blue moon, I just run it from query analyzer. If you have a need to run it more often, like for developer look ups, a UDF is definately the way to go.

I think you can get almost the same by using

select * from information_schema.columns

Regards...!

Source: Raymond Lewallen.

Thanks for the Data Dictionary SQL.
I'm trying to make a way for other developers in our group to generate data dictionaries for their databases without having to know much about the procedure. I'm even trying to encapsulate it in a SQL Reporting Services report where they enter a parameter of their database. The columns are reported, but the description from the FN_LISTEXTENDEDPROPERTY is not reported. The procedure only appears to work correctly when it resides in the database it's reporting from. Do I need to add a USE statement? Any suggestions?


CREATE Procedure dbo.prDataDictionary2
@Database varchar(50)
AS

SET @Database = @Database + '.dbo.'

declare @strSql nvarchar(4000)
Set @strSQL = '
create table #dd (
table_id int NULL,
table_name nvarchar(128) NULL,
column_order int NULL,
column_name varchar(60) NULL,
column_datatype varchar(20) NULL,
column_length int NULL,
column_description varchar(500) NULL
)

DECLARE @table_name nvarchar(128)

DECLARE tablenames_cursor CURSOR FOR
SELECT name FROM '+@Database+'sysobjects where type = ''U'' and status > 1 and name <> ''dtproperties'' order by name

OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
insert #dd select
o.[id] as ''table_id'',
o.[name] as ''table_name'',
0 as ''column_order'',
NULL as ''column_name'',
NULL as ''column_datatype'',
NULL as ''column_length'',
Cast(e.value as varchar(500)) as ''column_description''
from '+@Database+'sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N''MS_Description'',
N''user'',N''dbo'',N''table'', @table_name, null, default) e on o.name = e.objname
where o.name = @table_name

insert #dd select
o.[id] as ''table_id'',
o.[name] as ''table_name'',
c.colorder as ''column_order'',
c.[name] as ''column_name'',
t.[name] as ''column_datatype'',
c.[length] as ''column_length'',
Cast(e.value as varchar(500)) as ''column_description''
from '+@Database+'sysobjects o inner join '+@Database+'syscolumns c on o.id = c.id inner join '+@Database+'systypes t on c.xtype = t.xtype
left join ::FN_LISTEXTENDEDPROPERTY(N''MS_Description'',
N''user'',N''dbo'',N''table'', @table_name, N''column'', null) e on c.name = e.objname
where o.name = @table_name
order by c.colorder

FETCH NEXT FROM tablenames_cursor INTO @table_name
END

CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor

select a.table_name, table_description, column_name, column_datatype, column_length, column_description from
(select table_name, column_description table_description from #dd where column_order = 0) a,
(select * from #dd where column_order <> 0) b
where a.table_name = b.table_name

--select * from #dd
drop table #dd

Return'


EXEC sp_executesql @strSQL

This is precisely what I was looking for - thanks ! One question: I understand that the description column is pulled from the Description entry under Design View in Enterprise Manager. How do you script that entry? I'm thinking of migrating this to the production server, and all db changes have to go through the dba group in the form of scripts - so how would I do that?
Thanks for any help -

This is precisely what I was looking for - thanks ! One question: I understand that the description column is pulled from the Description entry under Design View in Enterprise Manager. How do you script that entry? I'm thinking of migrating this to the production server, and all db changes have to go through the dba group in the form of scripts - so how would I do that?
Thanks for any help -

I found the answer to my question, in case anyone else is interested. Use:

exec sp_addextendedproperty 'MS_Description',
'[put description here]',
'user',
'dbo',
'table',
'[put table name here]',
'column',
'[put field name here]'

OK, I'm a little late for this party. I had some T-SQL to generate schema documentation but this post added a few nice options to what I already had (mostly the extended properties concept).

Here is my contribution to this discusssion, one year later. Perhaps some people will stumble across it and find it useful. My data dictionary includes more details about the columns such as status flags for isnullable, isorimarykey, isforeignkey, isidentity, and table and column information for foreign key columns. It cannot be wrapped in a UDF like Oskar did above because it uses a temp table for the primary key information.

I'm not sure what will happen with line wraps on this post so watch for that.

/*

Purpose: T-SQL to create Data Dictionary
Created by: Joel Reinford
Date Created: Mar-4-2006
Modified by:
Date Modified:
Note: Some of this information can be obtained from the INFORMATION_SCHEMA views
Select c.* from INFORMATION_SCHEMA.COLUMNS c
Select t.* from INFORMATION_SCHEMA.TABLES t


*/



DECLARE @DataDictionary table(
RowID [int] identity(1,1) primary key
,TableName [varchar](200)
, ColumnName [varchar](200)
, OrdinalPosition int
, ColumnDefault [varchar](200)
, IsNullable [bit]
, DataType [varchar](20)
, CharacterMaximumLength int
, IsIdentity [varchar](5) --[bit]
, IsPrimaryKey [bit]
, IsForeignKey [bit]
, ReferencedTableName [varchar](200)
, ReferencedColumnName [varchar](200)
, ObjectDescription [varchar](500)
)


DECLARE @TableList table(
RowID [int] identity(1,1) primary key
,TableCatalog [varchar](200)
,TableSchema [varchar](200)
,TableName [varchar](200)
,TableType [varchar](20)
,ObjectDescription [varchar](500)
)

DECLARE
@RowNumber int
, @RowCount int
, @TableName varchar(255)
, @ObjectDescription varchar(500)


--create a temp table to hold the primary keys
--can't do INSERT EXEC with a table variable
IF object_id('tempdb..#PkColumns') is not null
BEGIN
DROP TABLE #PkColumns
END

CREATE TABLE #PkColumns (
RowID [int] identity(1,1) primary key
,table_Qualifier varchar(255) NOT NULL
, owner_name varchar(255) NOT NULL
, table_name varchar(255) NOT NULL
, column_name varchar(255) NOT NULL
, key_seq varchar(255) NOT NULL
, Pk_Name varchar(255) NOT NULL
)


--load the table list
INSERT @TableList
(
TableCatalog
,TableSchema
,TableName
,TableType
,ObjectDescription
)

SELECT
t.Table_Catalog
,t.Table_Schema
,t.Table_Name
,t.Table_Type
, CAST(IsNULL(e.value,'') as varchar(500))

FROM
INFORMATION_SCHEMA.TABLES t
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY
(
'MS_Description' --standard description property
,'user'
,'dbo'
,'table' --parent object type
, @TableName -- parent object name
, NULL --child object type
, NULL --child object name from above
) e
ON t.Table_Name = e.objname

WHERE
t.Table_Type = 'BASE TABLE'

SELECT @RowCount = Count(*) from @TableList


--loop through the tables
SET @RowNumber = 1
WHILE @RowNumber <= @RowCount
BEGIN

SELECT
@TableName = TableName
,@ObjectDescription = ObjectDescription
FROM
@TableList
WHERE
RowID = @RowNumber


--clear primary keys table
DELETE #PkColumns

--insert the primary key records retrieved by the system stored procedure
INSERT #PkColumns
EXEC sp_pkeys @table_name= @TableName


--insert the table name and description for header purposes
INSERT @DataDictionary
(
TableName
, ColumnName
, OrdinalPosition
, ColumnDefault
, IsNullable
, DataType
, CharacterMaximumLength
, IsIdentity
, IsPrimaryKey
, IsForeignKey
, ReferencedTableName
, ReferencedColumnName
, ObjectDescription
)

VALUES
(
@TableName --TableName
, '' --ColumnName
, 0 --OrdinalPosition
, '' --ColumnDefault
, 0 --IsNullable
, '' --DataType
, 0 --CharacterMaximumLength
, 0 --IsIdentity
, 0 --IsPrimaryKey
, 0 --IsForeignKey
, '' --ReferencedTableName
, '' --ReferencedColumnName
, @ObjectDescription --ObjectDescription
)

--insert the column schema information
INSERT @DataDictionary
(
TableName
, ColumnName
, OrdinalPosition
, ColumnDefault
, IsNullable
, DataType
, CharacterMaximumLength
, IsIdentity
, IsPrimaryKey
, IsForeignKey
, ReferencedTableName
, ReferencedColumnName
, ObjectDescription
)

SELECT
t.table_name
, c.column_name
, c.ordinal_position
, IsNULL(c.column_default, '')
, CASE WHEN c.is_nullable = 'Yes' THEN 1 ELSE 0 END
, c.data_type
, IsNULL(c.character_maximum_length, '')
, (SELECT COLUMNPROPERTY
(
OBJECT_ID(t.table_name)
,c.Column_Name,'IsIdentity'
)
) AS IsIdentity
, CASE WHEN pk.column_name IS NULL THEN 0
ELSE 1
END AS IsPrimaryKey
, CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0
ELSE 1
END AS IsForeignKey
, IsNULL(Fkey.REFERENCED_TABLE_NAME, '')
, IsNULL(Fkey.Referenced_Column_Name, '')
, CAST(IsNULL(e.value,'') as varchar(500)) as 'ColumnDescription'

FROM
information_schema.tables t
INNER JOIN information_schema.columns C
ON t.table_name = c.table_name
LEFT OUTER JOIN [#PkColumns] Pk
ON PK.column_Name = c.column_Name
LEFT OUTER JOIN
(
SELECT
CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
THEN 'Enabled'
ELSE 'Disabled' END
AS Status
, OBJECT_NAME(CONSTID) AS Constraint_Name
, OBJECT_NAME(FKEYID) AS Table_Name
, COL_NAME(FKEYID, FKEY) AS Column_Name
, OBJECT_NAME(RKEYID) AS Referenced_Table_Name
, COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name
FROM
SYSFOREIGNKEYS
) As Fkey
ON c.table_name = Fkey.table_name
AND c.column_Name = Fkey.Column_Name

LEFT JOIN ::FN_LISTEXTENDEDPROPERTY
(
'MS_Description' --standard description property
,'user'
,'dbo'
,'table' --parent object type
, @TableName -- parent object name from above
, 'column' -- child object type
, NULL -- child object name from above
) e
ON c.Column_Name = e.objname


WHERE
t.table_name = @TableName

ORDER BY
c.ordinal_position


SET @RowNumber = @RowNumber + 1

END --end table loop

--drop the temp table
DROP TABLE #PkColumns

SELECT *

FROM
@DataDictionary d

ORDER BY
d.TableName
, d.OrdinalPosition



Joel Reinford
Data Management Solutions LLC

Just a question.
There is any easy workaround for collation problems?

Thanks!

Ray,
Perhaps Christian is talking about this:
-- Fresh install of SQL Server 2005
-- DBs from a previous installation of MSDE.

When I run the scripts from your blog, I get this error message:

Msg 468, Level 16, State 9, Line 68
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

My admin expertise for SQL Server is low.  So far my research hasn't revealed anything conclusive or useful.

Any ideas?

I discovered and fixed another problem. Also, I fixed my COLLATE error problem.  Please read on...

New problem; Fixed:
If I use the first SELECT statement as it's written, I get no rows back when the script completes.  However, it works if I remove the "status > 1" part of the WHERE clause.  So the new SELECT statement looks like this:

SELECT name FROM sysobjects where type = 'U'
order by name

I also noticed the STATUS column is undocumented.  Why are you guys using it?


Workaround for Original Problem:
Just before the 2 WHERE clauses that read
    where o.name = @table_name

I inserted
   COLLATE Latin1_General_CI_AS

So, the script reads
   [FROM clause with Join info]
   COLLATE Latin1_General_CI_AS
   where o.name = @table_name

Sadly, this tweak did not work for Oskar's embellished Function near the top of this thread.

Cheers!
Rick in Boston, MA

How we can generate the data dictionary (sql script) with column description??

I modified Oskars script for sql 2005

/*  

Oskar Austegard

Here it is as a function returning a table (useful for queries like this:

select *

from dbo.fnDataDictionary()

where column_description is null

SQL 2005 changes

1) Add collate twice

  COLLATE Latin1_General_CI_AS

2) used sys.objects not sysobjects

3) Removed status = 1 since

  status is 0 in 2005 system view sysobjects

  status is not in sys.objects

*/

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE FUNCTION fnDataDictionary ()

RETURNS @DataDictionary TABLE (

table_id int NULL,

table_name nvarchar(128) NULL,

column_order int NULL,

column_name varchar(60) NULL,

column_datatype varchar(20) NULL,

column_length int NULL,

column_description varchar(500) NULL

)

AS

BEGIN

DECLARE @table_name nvarchar(128)

DECLARE tablenames_cursor CURSOR FOR

SELECT name FROM sys.objects where type = 'U' order by name

OPEN tablenames_cursor

FETCH NEXT FROM tablenames_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0

BEGIN

insert @DataDictionary

select

o.[id] as 'table_id',

o.[name] as 'table_name',

0 as 'column_order',

NULL as 'column_name',

NULL as 'column_datatype',

NULL as 'column_length',

Cast(e.value as varchar(500)) as 'column_description'

from sysobjects o

left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, null, default) e

on o.name = e.objname

COLLATE Latin1_General_CI_AS

where o.name = @table_name

insert @DataDictionary

select

o.[id] as 'table_id',

o.[name] as 'table_name',

c.colorder as 'column_order',

c.[name] as 'column_name',

t.[name] as 'column_datatype',

c.[length] as 'column_length',

Cast(e.value as varchar(500)) as 'column_description'

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', @table_name, N'column', null) e

on c.name = e.objname

COLLATE Latin1_General_CI_AS

where o.name = @table_name

order by c.colorder

FETCH NEXT FROM tablenames_cursor INTO @table_name

END

CLOSE tablenames_cursor

DEALLOCATE tablenames_cursor

RETURN

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Summary Data Dictionary Creator (DDC) is a simple application which helps you document SQL Server databases.

Can someone tell me if both the sysobject/syscolumns and the extended property data is stored within the corresponding dbase?  Or is it stored in Master or msdb?  I'm trying to determine if my DR stategy needs to change if I begin to use this in a prod environment.

I can recommend SqlSpec as a very comprehensive data dictionary generator.  It's available at http://www.elsasoft.org

how can i create data dictionary of required/selected tables from data base in sql server

how can create Data dictionary in sql server 2005

i have prblem in above script it gives error in

insert DataD select

and

insert DataD select  

what is the syntex of this in sql server 2005

plz give me answer very urgent

Hi I am not able to run this code from .sql file using MS SQL 2005. I got an error

Msg 468, Level 16, State 9, Line 20

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Msg 468, Level 16, State 9, Line 33

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Please help to resove the problem

Generate Sql Server data dictionary table

If you're getting error

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

then change the joins to

o.name COLLATE Latin1_General_CI_AS = e.objname

forums.microsoft.com/.../ShowPost.aspx

Pingback from  Quick Data Dictionary &laquo; Jeff Maass&#8217;s Development Toolkit

I just tried the fnDataDictionary function suggested above, and found that it returns 2 rows for each column with the user-defined data type. One row shows the UDDT, the other shows the base data type. Cheers

This function(s) only return information on the dbo. schema.

COLLATE Latin1_General_CI_AS - Works Wonders! Thanks for the great solution.