September 2, 2010 at 2:58 pm
We recently upgraded from 2000 to 2008. The SQL statement I used to create my version of a Data Dictionary fails. I've scrounged posts here and elsewhere with no luck, mostly because I don't understand enough of SQL Admin. FYI - The database is a 3rd party ERP App.
What needs to change is:
1. Replace the sysobjects Join with an extended_properties Join, and, sysobjects.value with the extended_properties description.
2. Replace xtype = 'U' with whatever is equivalent in 2008.
I copy the results into Excel and massage the last few columns a little. After the massaging the resulting Data Type column looks something like the following examples and the _precision and _scale are deleted:
Is <- small integer
I 10 <- integer
C 40v
N 17,7
N 5,2
FYI - This format is a carry over from my 1980's data dictionary format.
The final columns are:
Table, Column, Pos, Null?, Data Type with size attributes, description
Here's the SQL Server 2000 statement:
SELECT
table_name,
column_name,
ordinal_position,
is_nullable,
CASE data_type
WHEN 'char' THEN 'C'
WHEN 'varchar' THEN 'Cv'
WHEN 'datetime' THEN 'D'
WHEN 'smalldatetime' THEN 'Ds'
WHEN 'float' THEN 'flt'
WHEN 'image' THEN 'img'
WHEN 'int' THEN 'I'
WHEN 'smallint' THEN 'Is'
WHEN 'decimal' THEN 'N'
WHEN 'numeric' THEN 'Num'
WHEN 'nvarchar' THEN 'Cvn'
WHEN 'text' THEN 'txt'
WHEN 'tinint' THEN 'It'
ELSE data_type
END As DataType,
CASE data_type
WHEN 'char' THEN cast(character_maximum_length as char(4))
WHEN 'varchar' THEN cast(character_maximum_length as char(4))
WHEN 'nvarchar' THEN cast(character_maximum_length as char(4))
WHEN 'decimal' THEN cast(numeric_precision as char(2)) + '.' + cast(numeric_scale as char(2))
ELSE ''
END as Len,
numeric_precision,
numeric_scale,
cast(sysprops.value as varchar(255)) AS remarks
FROM
INFORMATION_SCHEMA.COLUMNS
LEFT OUTER JOIN sysobjects on sysobjects.name = information_schema.columns.table_name,
sysproperties AS sysprops
WHERE
TABLE_SCHEMA = 'dbo' and xtype = 'U' and table_name <> 'dtproperties'
ORDER BY
Table_Name,
column_name
Would appreciate anyone's help with modifying this for SQL Server 2008.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 7, 2010 at 2:39 pm
Figured it out.
Here's a SQL Command to create a simple Data Dictionary. The Results columns are:
Table Name
Column
Ordinal Position
Data Type - Abbreviated data type and field length/attributes
Remarks - MS_Description (Note: I know our longest description is 249 characters)
SELECT table_name As TableName,
column_name ColumnName,
ordinal_position As Ord,
CASE data_type
WHEN 'char' THEN 'C ' + CAST(character_maximum_length AS CHAR(4))
WHEN 'varchar' THEN 'C ' + RTRIM(CAST(character_maximum_length AS CHAR(4))) + 'v'
WHEN 'datetime' THEN 'D'
WHEN 'smalldatetime' THEN 'Ds'
WHEN 'float' THEN 'Flt'
WHEN 'image' THEN 'img'
WHEN 'int' THEN 'I'
WHEN 'smallint' THEN 'Is'
WHEN 'decimal' THEN 'N '+ CAST(numeric_precision AS CHAR(2)) + '.' + CAST(numeric_scale AS CHAR(2))
WHEN 'numeric' THEN 'Num' + CAST(numeric_precision AS CHAR(2)) + '.' + CAST(numeric_scale AS CHAR(2))
WHEN 'nvarchar' THEN 'C ' + CAST(character_maximum_length AS CHAR(4)) + 'vn'
WHEN 'text' THEN 'Txt'
WHEN 'tinyint' THEN 'It'
ELSE data_type
END AS Type,
CAST(isnull(SysExtProp.VALUE,'') AS VARCHAR(250)) AS Remarks
FROM information_schema.columns InfScm
LEFT OUTER JOIN sysobjects
ON sysobjects.name = InfScm.table_name
LEFT OUTER JOIN sys.extended_properties SysExtProp
ON SysExtProp.major_id = sysobjects.id
AND SysExtProp.minor_id = InfScm.ordinal_position
AND SysExtProp.name = 'MS_Description'
WHERE table_schema = 'dbo'
AND xtype = 'U'
AND table_name <> 'dtproperties' and TABLE_NAME <> 'sysdiagrams'
ORDER BY
table_name,
column_name
HTH Someone.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply