SQL server 2005 Data dictionary

Posted on 2011-03-02
Last Modified: 2012-06-21
Aloha, I want to create a data dictionary and be able to print it out. I know I can put the descriptions in the extended properties or description in the databse diagram but how can I access these properties using sql? mahalo
Question by:Wonderwall
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 17

Expert Comment

ID: 35023967
it has been so long since I visited the islands.... alas....

Not sure exactly how you want to approach this, but first I would suggest 'database diagrams'.  Go into SSMS, Databases, select your database and then see 'Database Diagrams' within the hierarchy.  Right click it, and choose to create a new diagram.  You will be given the option to select one or multiple tables, and then choose 'Add'.

It will push the chosen tables to the diagram, with keys referenced in each table.  You could then right click the diagram and choose to 'Show Relationship Labels', add text annotations (like table descriptions, business reference), and even copy it to the clip board for use within a graphics editor of your choosing.

Then, of course, you've always got 'sp_help'.  This is the system proc, you run it by object, like this:

EXEC sp_help 'tablename'

If you didn't want to run it selectively, per table, you could always cursor through the tables, pumping results to temp table, and then loop through the temp table to retrieve the data, per table.

The code I've posted below is also handy.  It generates the table definition (create statement) for the give @tablename.

IF OBJECT_ID('usp_generateScript','p')>0
DROP PROC dbo.usp_GenerateScript
CREATE PROC dbo.usp_GenerateScript ( 
@tableName varchar(100)) 
Allows for the creation of table definition.
EXEC dbo.usp_GenerateScript @tablename = 'AceData'
	IF EXISTS(Select 1 from Information_Schema.COLUMNS where Table_Name=@tableName) 
		declare @sql varchar(8000) 
		declare @table varchar(100) 
		declare @cols table (datatype varchar(50)) 
		insert into @cols values('bit') 
		insert into @cols values('binary') 
		insert into @cols values('bigint') 
		insert into @cols values('int') 
		insert into @cols values('float') 
		insert into @cols values('datetime') 
		insert into @cols values('text') 
		insert into @cols values('image') 
		insert into @cols values('uniqueidentifier') 
		insert into @cols values('smalldatetime') 
		insert into @cols values('tinyint') 
		insert into @cols values('smallint') 
		insert into @cols values('sql_variant') 

		set @sql='' 
		Select @sql=@sql 
		+case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type + 
		case when Data_Type in (Select datatype from @cols) then '' else '(' end 
		+case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+ 
		','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) 
		end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end 
		+case when Data_Type in (Select datatype from @cols)then '' else ')' end 
		+case when Is_Nullable='No' then ' Null,' else ' Not null,' end 
		from Information_Schema.COLUMNS where Table_Name=@tableName 
		select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName 
		select @sql=@table + substring(@sql,1,len(@sql)-1) +' )' 
		select @sql as DDL 
		Select 'The table '+@tableName + ' does not exist' 




Open in new window

LVL 17

Accepted Solution

dbaSQL earned 500 total points
ID: 35023979
The sp_help procedure will return the extended properties you're looking for.  The procedure I posted will return the actual table create statement, for the given @tablename.  Maybe more than you need, but that will include the properties as well.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question