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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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 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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

14 Experts available now in Live!

Get 1:1 Help Now