Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Data Dictionary from within SQL Server 2000 Expand / Collapse
Author
Message
Posted Thursday, February 7, 2002 12:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 7, 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
Posted Sunday, February 10, 2002 9:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Monday, February 11, 2002 4:37 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
I have found this usefull already and have been using it a while. Good article on the subject.




Post #27797
Posted Tuesday, February 12, 2002 12:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:53 PM
Points: 33,063, Visits: 15,179
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
Posted Thursday, February 14, 2002 8:01 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 7, 2007 8:30 AM
Points: 74, Visits: 1
Thanks for all your comments!

Mindy




Post #27799
Posted Tuesday, March 26, 2002 6:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363
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
Posted Sunday, June 16, 2002 11:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 14, 2002 5:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:54 PM
Points: 269, Visits: 467
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
Posted Thursday, August 15, 2002 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, November 4, 2002 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse