SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Table - Column - Enterprise Mgr. Descriptions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EM99
Starting Member

USA
1 Posts

Posted - 04/22/2004 :  18:58:43  Show Profile  Reply with Quote
I wrote this quick SQL for project documentation purposes.

/*
Provides Table Name, Column Name, Extended Description
*/
select
a.name as tbl_name,
b.name as column_name,
d.name as data_type,
d.length as length,
d.xprec as prec,
d.scale as scale,
b.usertype,
b.scale,
c.value
from sysobjects as a
inner join
syscolumns as b
on a.id = b.id
inner join
sysproperties as c
on b.colid = c.smallid and a.id = c.id
inner join
systypes as d
on b.xtype = d.xtype

X002548
Not Just a Number

15586 Posts

Posted - 04/23/2004 :  12:12:08  Show Profile  Reply with Quote
M$

beat you to it...

SELECT * FROM INFORMATION_SCHEMA.Columns





Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/25/2004 :  11:08:14  Show Profile  Reply with Quote
quote:
Originally posted by X002548

beat you to it...

SELECT * FROM INFORMATION_SCHEMA.Columns


Do you know if there is an INFORMATION_SCHEMA view-thingie that JOINs syscolumns/sysobjects to sysproperties to get the extended descriptions? That would be handy ...

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2004 :  12:46:16  Show Profile  Reply with Quote
What's in sysproperties?

It's not documented...or at least I can't find it...

Check here

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20914081.html



Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/26/2004 :  14:14:40  Show Profile  Reply with Quote
quote:
Originally posted by X002548

What's in sysproperties?


If you put in a descriptive-name, when editing Columns or Tables in Enterprise Manager, they get stored in sysproperties - which is a very basic "container table" designed to store anything about anything. Somewhat of a "normalisation-is-not-for-us-pros" type solution!
quote:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20914081.html


Thanks, that's pretty much what I'm doing, but I am trying to break the habit of using JOINs to SYSxxx tables in favour of INFORMATION_SCHEMA to guarantee<g> future-proofing.

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2004 :  14:24:59  Show Profile  Reply with Quote
I guess you're doing this to store table and column definitions...

I'd keep that separate as part of the whole data model...

Are you using that for application help?

Like, [F1] what's this?



Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/26/2004 :  15:55:58  Show Profile  Reply with Quote
quote:
Originally posted by X002548

I guess you're doing this to store table and column definitions...

I'd keep that separate as part of the whole data model...

Are you using that for application help?

Like, [F1] what's this?

Sort of. We do have a set of tables that contain a superset of the database meta information (such as data types like ALL CAPS), but I wanted to use the standard MS table and column 'descriptive' definitions so that any APPs that utilised them would benefit from whatever richer-descriptions my developers had bothered to enter.

But the whole thing seems such a ghastly hack, with no relevant INFORMATION_SCHEMA table/view, that i wonder if anything is going to use this repository.

The particular question I had in mind was to do with the fact that I had a "snippet" that uses our own tables to show a Descriptive Name for a column, but in the event that our Descriptive Stuff Table is not found (e.g. in "master" or a client designed database<g>) my scripts would offer stuff from sysproperties, for completeness (but preferably the equivalent INFORMATION_SCHEMA table/view)

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/28/2004 :  13:53:10  Show Profile  Reply with Quote
Want hack...

Go look at some of the system sprocs...

start with sp_help....

Actual code

quote:

-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @objid = xusertype from systypes where name = @objname

-- IF NOT IN SYSTYPES, GIVE UP
if @objid is null
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return(1)
end



Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000