[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

SQL server 2005 Data dictionary

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
Wonderwall
Asked:
Wonderwall
  • 2
1 Solution
 
dbaSQLCommented:
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
 
dbaSQLCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now