Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
EM99
Starting Member
1 Post |
 Posted - 2004-04-22 : 18:58:43
|
| 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.valuefrom sysobjects as a inner join syscolumns as b on a.id = b.idinner join sysproperties as c on b.colid = c.smallid and a.id = c.idinner join systypes as don b.xtype = d.xtype |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-23 : 12:12:08
|
M$beat you to it...SELECT * FROM INFORMATION_SCHEMA.Columns Brett8-) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-25 : 11:08:14
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-26 : 14:14:40
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-26 : 14:24:59
|
| 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?Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-26 : 15:55:58
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-28 : 13:53:10
|
Want hack...Go look at some of the system sprocs...start with sp_help....Actual codequote:
-- 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
Brett8-) |
|
|
|
|
|
|
|
|
Table - Column - Enterprise Mgr. Descriptions