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
  • 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now