473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

System Schema & Documentation

Anyone know how to query sysobjects & syproperties showing the table,
field name, and field properties (specifically the description)? I
have so far:

SELECT *
FROM sysproperties sp
INNER JOIN sysobjects so
ON sp.id = so.id

SELECT *
FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.type = 'U'

....not sure how to join the two. The 'ID' column refers to the table
ID and not the field ID.
Jul 20 '05 #1
4 3923
"ckdinternet" <ck*********@yahoo.com> wrote in message
news:4e**************************@posting.google.c om...
Anyone know how to query sysobjects & syproperties showing the table,
field name, and field properties (specifically the description)? I
have so far:

SELECT *
FROM sysproperties sp
INNER JOIN sysobjects so
ON sp.id = so.id

SELECT *
FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.type = 'U'

...not sure how to join the two. The 'ID' column refers to the table
ID and not the field ID.

Try this:

select substring(o.name,1,50) as "Table Name",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length
from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)

--where substring(o.name,1,250) = @param

order by 1,2

Pete Brown
Falls Creek.
Oz
www.mountainman.com.au/software
Jul 20 '05 #2
ck*********@yahoo.com (ckdinternet) wrote in message news:<4e**************************@posting.google. com>...
Anyone know how to query sysobjects & syproperties showing the table,
field name, and field properties (specifically the description)? I
have so far:

SELECT *
FROM sysproperties sp
INNER JOIN sysobjects so
ON sp.id = so.id

SELECT *
FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.type = 'U'

...not sure how to join the two. The 'ID' column refers to the table
ID and not the field ID.


Here is one possible query:

select c.table_name, c.column_name, p.value
from information_schema.columns c
join sysproperties p
on object_id(c.table_name) = p.id
and c.ordinal_position = p.smallid
order by c.table_name, c.column_name

Note that sysproperties is not documented, so in theory you shouldn't
reference it in code if possible, or at least not in production code.
But in this case, the only alternative is to use
fn_listextendedproperties(), which works fine for retrieving
individual properties, but is very difficult to use when you need
multiple properties in a set-based query.

Simon
Jul 20 '05 #3
Thanks for the replies.

I did this and it worked great! (off the record)

SELECT tab.name, col.name, prop.value
FROM sysobjects tab
INNER JOIN syscolumns col ON tab.id = col.id
LEFT OUTER JOIN sysproperties prop ON col.id = prop.id AND col.colid =
prop.smallid
WHERE tab.xtype = 'U'

C

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4
I added a little based on another Groups post I found to list all the
column properties and descriptions of every table in my database using
this query:

--make a temporary table to hold list of table names
CREATE table #tablelist (table_name varchar(20))

INSERT INTO #tablelist --find all tables in the database SELECT
table_name FROM information_schema.tables WHERE table_type='BASE TABLE'

--create a cursor loop over temp table to get all meta data

DECLARE @table_name varchar(20)
DECLARE table_Cursor CURSOR FOR
SELECT table_name from #tablelist
OPEN table_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_Cursor into @table_name
SELECT
table_name,
column_name,
data_type,
cast(des.value AS VARCHAR(4000)) AS col_desc
FROM information_schema.Columns col
LEFT OUTER JOIN

::fn_listextendedproperty(NULL,'user','dbo','table ',@table_name,'column',default)
des
ON col.column_name=des.objname
WHERE table_name=@table_name
ORDER BY ORDINAL_POSITION

END
CLOSE table_Cursor
DEALLOCATE table_Cursor

DROP TABLE #tablelist


Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my...
0
by: Almoni | last post by:
Hi, I have a few .xsd files that include each other in the following way: <!-- lets call the main schema file AA.xsd and it includes BB.xsd inside it --> <xs:schema...
0
by: XSD-optimist | last post by:
I am trying to generate the classes for an XSD schema using the Microsoft XSD Object Code Generator (XSDObjGen). I am having a schema that contains the definition of the following: 1. a complex...
0
by: Deep Purple | last post by:
I started with the following error... ------------------------------------------------------- An error occured while loading the schema with TargetNamespace 'http://www.w3.org/2001/XMLSchema'...
1
by: comic_rage | last post by:
Hi, I am trying to add an annotation with a documentation, which contains several nodes. So far, I having a problem on how to code this with C#. Any help is appreciated. <xsd:annotation>...
2
by: comic_rage | last post by:
how do you add a comment line/section to an xml schema xsd file? like this <!-- =============================================================== --> <!-- =================== My comment line ...
7
by: Robert Stearns | last post by:
I ran the following bit of SQL and my PRIMARY KEY wound up in schema SYSIBM called SQL.... not schema is3 called primary. The index registation did wind up there. Obviously there's something I...
1
by: Marc | last post by:
Hi! I'm working with a C# client that calls a php web service. I've created a wrapper to call the service using .NET wsdl tool (adding a web reference). The call to the server works fine, it...
0
by: decker | last post by:
For a while now, I have been accustomed to defining my application's configuration values in the application configuration file in a custom section. In the implementation of my...
0
by: mk189 | last post by:
Hi, I am trying to create XML schema of custom markup language, enriched by XHTML. In simplified version, the XML documet could look like that: <a:alarm-manual xmlns:a="alarm-manual"...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.