Data Dictionary from within SQL Server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Curnutt Mindy » Data Dictionary from within SQL Server 2000

30 posts, Page 1 of 3

1 2 3 »»»

Data Dictionary from within SQL Server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

mcurnutt

Posted Thursday, February 07, 2002 12:00 AM

Valued Member

Group: General Forum Members
Last Login: Friday, September 07, 2007 8:30 AM
Points: 74, Visits: 1

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcurnutt/datadictionaryfromwithinsqlserver2000.asp

Post #2550

ckempste

Posted Sunday, February 10, 2002 9:43 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

Nice article Mindy, very informative.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Post #27796

Antares686

Posted Monday, February 11, 2002 4:37 AM

SSCrazy Eights

Group: Moderators
Last Login: Today @ 10:09 AM
Points: 8,357, Visits: 685

I have found this usefull already and have been using it a while. Good article on the subject.

Post #27797

Steve Jones - SSC Editor

Posted Tuesday, February 12, 2002 12:26 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

Nice article. I too tried this last year, but was a little ticked that I had to use a cursor to get this info. This should be queryable for all tables, but ...

Anyway, well written and a useful technique.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #27798

mcurnutt

Posted Thursday, February 14, 2002 8:01 AM

Valued Member

Group: General Forum Members
Last Login: Friday, September 07, 2007 8:30 AM
Points: 74, Visits: 1

Thanks for all your comments!

Mindy

Post #27799

G.R.Prithiviraj Kulasingham

Posted Tuesday, March 26, 2002 6:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 21, 2012 8:59 PM
Points: 421, Visits: 362

I saw your article only today.
It is very usefull, and practical.
I learned so many new things in your article.

Thanks, Keep it up.

Preethi

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Post #27800

swampy

Posted Sunday, June 16, 2002 11:55 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 16, 2002 12:00 AM
Points: 4, Visits: 1

Thankyou - this is exactly the answer I have been looking for!!

Post #27801

bnordberg

Posted Wednesday, August 14, 2002 5:01 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 267, Visits: 353

Great idea. I usually find it better to use the information_schema views so I altered your stored procedure as follows

select
o.[table_name] as 'table_name',
c.ordinal_position as 'column_order',
c.[column_name] as 'column_name',
e.value as 'column_description',
C.DATA_TYPE as Type,
coalesce(numeric_precision,character_maximum_length,0) as Length,
coalesce(numeric_scale,0) as [Decimal Pos],
C.COLUMN_DEFAULT AS [Default]
from information_schema.tables o inner join information_schema.columns c on o.table_name =
c.table_name
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table', @table_name, N'column', null) e on c.column_name = e.objname
where o.table_name = @table_name

Thanks

Post #27802

DirkFrazier

Posted Thursday, August 15, 2002 7:41 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 20, 2004 5:27 AM
Points: 10, Visits: 1

I've been exploring this topic for a few days and noticed that the system table sysproperties contained records identified as MS_Description with <Binary> data in the 'value' column. I wrote the following query which returns these records matched with their corresponding records in sysobjects and syscolumns:

SELECT TOP 100 PERCENT dbo.sysobjects.name AS [Table Name], dbo.syscolumns.name AS [Field Name], dbo.systypes.name AS [Data Type],
dbo.syscolumns.length,
dbo.syscolumns.prec AS [Precision], CONVERT(varchar(500), dbo.sysproperties.[value]) AS Description

FROM
dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND dbo.syscolumns.colid = dbo.sysproperties.smallid INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype

GROUP BY dbo.sysobjects.name, dbo.syscolumns.colid, dbo.syscolumns.name, dbo.systypes.name, dbo.syscolumns.length, dbo.syscolumns.prec,
dbo.sysproperties.[value]

ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

I'm pretty new at this stuff, so my question is, is this any different than Mindy's solution or am I missing something?

Post #27803

Peter Brightman

Posted Monday, November 04, 2002 6:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 2002 12:00 AM
Points: 2, Visits: 1

Article is cool, now i know how to query the data dictionary. However i have created my own SQL statement that i use with MS Query, where i cannot define stored procedures but just SQL statements. Here are two simple statements that queries 1. all tables, 2. all tables with all columns.

1. tables

SELECT sysobjects.name 'Table-name'
FROM sysobjects sysobjects
WHERE (sysobjects.type='U') AND (sysobjects.status>1)
ORDER BY sysobjects.name

2. tables with columns

SELECT sysobjects.name 'Table-name', syscolumns.name 'Column-name', syscolumns.length, syscolumns.xprec, syscolumns.xscale, syscolumns.colid, syscolumns.bitpos, syscolumns.isnullable
FROM syscolumns syscolumns, sysobjects sysobjects
WHERE (sysobjects.type='U') AND (sysobjects.id=syscolumns.id)
ORDER BY sysobjects.name, syscolumns.colid

cheers, Peter

Post #27804

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions