?
Solved

SQL server 2005 Data dictionary

Posted on 2011-03-02
2
Medium Priority
?
357 Views
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
0
Comment
Question by:Wonderwall
[X]
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
2 Comments
 
LVL 17

Expert Comment

by:dbaSQL
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
GO
CREATE PROC dbo.usp_GenerateScript ( 
@tableName varchar(100)) 
AS
SET NOCOUNT ON;
/*
Allows for the creation of table definition.
EXEC dbo.usp_GenerateScript @tablename = 'AceData'
*/
BEGIN
	IF EXISTS(Select 1 from Information_Schema.COLUMNS where Table_Name=@tableName) 
	BEGIN 
		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 
	END
	ELSE
	BEGIN
		Select 'The table '+@tableName + ' does not exist' 
	END

END

SET NOCOUNT OFF;

GO

Open in new window

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 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