I agree with JonGalloway.ToString().
Data Dictionary Creator is the best handy tool to generate Data Dictionary with muliptle import/export functionality.
Awesome.
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.
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.
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
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.
/*
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
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.
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.
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
@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
)
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
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 -
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
Yes, the information_schema does return the same data, but that’s not nearly as fun to do Also, you can’t exclude Views when using information schema. I’ve never generated a data dictionary that included views. Also, you can’t get extended property information from information_scheme.columns without going through the process above of using the function FN_LISTEXTENDEDPROPERTY. The code above is much better for producing data dictionaries rather than the information_schema.columns.
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 agree with JonGalloway.ToString().
Data Dictionary Creator is the best handy tool to generate Data Dictionary with muliptle import/export functionality.
Awesome.
This post has helped a lot.
Have a Happy Holidays.
How about a tool that generates beautiful meta-data documentation and all for free. Check this sample document
http://www.sqldocumentor.com/sqlserverhtml/sql_server_index.html
Few more days for release. Please submit your feedback
Thanks
Nilesh
http://www.sqldocumentor.com
COLLATE Latin1_General_CI_AS – Works Wonders! Thanks for the great solution.
This function(s) only return information on the dbo. schema.
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
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
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177141&SiteID=1
Ok, finally ran into the COLLATION issue. In the joins for pulling descriptions, change them to read:
on o.name COLLATE Latin1_General_CI_AS = e.objname
and
on c.name COLLATE Latin1_General_CI_AS = e.objname
Sorry guys, but I haven’t had any issues with running this in Sql Server 2005.
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
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
how can i create data dictionary of required/selected tables from data base in sql server
I can recommend SqlSpec as a very comprehensive data dictionary generator. It’s available at http://www.elsasoft.org
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 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
How we can generate the data dictionary (sql script) with column description??
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
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?
Christian,
What collation problems are you referring to? Are you just wanting the database to list which collation schemes are being used?
Just a question.
There is any easy workaround for collation problems?
Thanks!
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
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]‘
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 -
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
Yes, the information_schema does return the same data, but that’s not nearly as fun to do
Also, you can’t exclude Views when using information schema. I’ve never generated a data dictionary that included views. Also, you can’t get extended property information from information_scheme.columns without going through the process above of using the function FN_LISTEXTENDEDPROPERTY. The code above is much better for producing data dictionaries rather than the information_schema.columns.
I think you can get almost the same by using
select * from information_schema.columns
Regards…!
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.
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