Using T-SQL or SQL script can we create a data dictionary

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Sure it is possible. You could use the information_schema and sys views.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 2

Here is what I use to create a data dictionary using T-SQL.

Paxman SSC Enthusiast Points: 117 More actions · Answer 3

Here is part of what I do...

CREATE VIEW [dbo].[DataDictonary] AS

SELECT schemas.name AS SchemaName

,all_objects.name AS TableName

,syscolumns.id AS ColumnId

,syscolumns.name AS ColumnName

,systypes.name AS DataType

,syscolumns.length AS CharacterMaximumLength

,sysproperties.[value] AS ColumnDescription

,syscomments.TEXT AS ColumnDefault

,syscolumns.isnullable AS IsNullable

FROMsyscolumns

INNER JOIN sys.systypes ON syscolumns.xtype = systypes.xtype

LEFT JOIN sys.all_objects ON syscolumns.id = all_objects.[object_id]

LEFT OUTER JOIN sys.extended_properties AS sysproperties ON (sysproperties.minor_id = syscolumns.colid AND sysproperties.major_id = syscolumns.id)

LEFT OUTER JOIN sys.syscomments ON syscolumns.cdefault = syscomments.id

LEFT OUTER JOIN sys.schemas ON schemas.[schema_id] = all_objects.[schema_id]

WHERE syscolumns.id IN (SELECT id

FROM sysobjects

WHERE xtype = 'U')

AND (systypes.name <> 'sysname')

Bruce Dunwiddie SSC Journeyman Points: 83 More actions · Answer 4

SSC Journeyman

Points: 83

May 1, 2014 at 12:54 am

vikas.gupta7878 SSC Enthusiast Points: 126 More actions · Answer 5

Hi,

Please let me know the free download version from this site will work how many days and what are limitations?

vikas.gupta7878 SSC Enthusiast Points: 126 More actions · Answer 6

Bruce Dunwiddie (5/1/2014)
I use http://www.sqldatadictionary.com/ .

Hi,

Please let me know the free download version from this site will work how many days and what are limitations?

pitt SSC Enthusiast Points: 156 More actions · Answer 7

We use Dataedo, it has nice output, you can format text and paste images and group objects into logical "modules".