Who knows the GetSchema method well?

  • Thread starter Thread starter HillBilly
  • Start date Start date
H

HillBilly

I need to get the Description meta data from a SQL 2005 table. In other
words I'm looking for the space delineated text in the Description i.e.
First Name that is a description for the FirstName column. I don't know how
to find the Description meta data or put it into a TableAdapter.
 
HillBilly said:
I need to get the Description meta data from a SQL 2005 table. In other
words I'm looking for the space delineated text in the Description i.e.
First Name that is a description for the FirstName column. I don't know
how to find the Description meta data or put it into a TableAdapter.
Click to expand...

I am not sure you can this directly with the framework.
But you can have a look at :
fn_listextendedproperties
and
sys.extended_properties
in SQL 2005
 
I still use SQL DMO/SMO for iterating through SQL objects. You can find info
on using these objects in SQL books online. There are also samples you can
install when you initially download.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************
 
This is an example from my book. It will need a few hookups to the form
controls, but the core of the functionality is all here. There are examples
of each way the GetSchema method works.

hth

'Copyright (c) Beta V Corporation
'All rights reserved.
' This demonstration program is provided "AS-IS" with no warranties
expressed or implied.
' Built with SQL Server 2005 and Visual Studio 2005.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
Dim cn As SqlConnection
' Dim cmd As SqlCommand
Dim ds As DataSet
Dim dtRestrictions As DataTable
Dim dtCurrentRest As DataTable
Dim strARestrictions() As String = {}

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cn = New SqlConnection("data source=demoserver;integrated
security=sspi;database=biblio;")
cn.Open()
SetupRestrictionsTable()
Dim dtDatabases As DataTable
dtDatabases = cn.GetSchema("Databases")
cmbDatabases.ValueMember = "Database_Name"
cmbDatabases.DataSource = dtDatabases
End Sub

Private Sub SetupRestrictionsTable()
dtRestrictions = cn.GetSchema("Restrictions") ' Get
MetaDataCollection restrictions
Dim colValue As New DataColumn("Value", GetType(String))
dtRestrictions.Columns.Add(colValue)
End Sub
Private Sub ShowSchemaData()
ds = New DataSet
ds.Locale = System.Globalization.CultureInfo.InvariantCulture ' To
make FxCop happy
Dim dtRoot As DataTable = cn.GetSchema ' TableName
"MetaDataCollections"
For Each dr As DataRow In dtRoot.Rows
Dim dt As New DataTable(dr("CollectionName").ToString)
dt.Locale = System.Globalization.CultureInfo.InvariantCulture
dt = cn.GetSchema(dr("CollectionName").ToString)
ds.Tables.Add(dt)
cmbTables.Items.Add(dt.TableName)
Next
cmbTables.SelectedIndex = 0
DataGridView1.DataSource = ds.Tables(0)
End Sub

Private Sub cmbTables_SelectedValueChanged(ByVal sender As Object, ByVal
e As System.EventArgs) Handles cmbTables.SelectedValueChanged
Dim dt As DataTable = ds.Tables(cmbTables.Text)
DataGridView1.DataSource = dt
Dim strN As String = "CollectionName = '" & dt.TableName & "'"
Dim drA() As DataRow = dtRestrictions.Select(strN)
If drA.GetLength(0) > 0 Then
Dim dv As DataView = dtRestrictions.DefaultView
dv.RowFilter = "CollectionName='" & dt.TableName & "'"
dtCurrentRest = dv.ToTable ' Copy DataView rows
to temp table
ReDim strARestrictions(dv.Count)
dgvRestrictions.DataSource = dtCurrentRest
dgvRestrictions.Visible = True
Else
dgvRestrictions.DataSource = Nothing
dgvRestrictions.Visible = False
End If
End Sub

Private Sub cmbDatabases_SelectedValueChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles cmbDatabases.SelectedValueChanged
If cmbDatabases.Text <> "" Then cn.ChangeDatabase(cmbDatabases.Text)
ShowSchemaData()
End Sub
Private Sub dgvRestrictions_CellEndEdit(ByVal sender As Object, ByVal e
As System.Windows.Forms.DataGridViewCellEventArgs) Handles
dgvRestrictions.CellEndEdit
If e.ColumnIndex = 4 Then ' Only permit changes to the Value
dgvRestrictions.EndEdit()
ReDim strARestrictions(dtCurrentRest.Rows.Count - 1) '
0-based Array
For Each dr As DataRow In dtCurrentRest.Rows
If Not IsDBNull(dr.Item("Value")) Then
If Len(dr("Value")) = 0 Then
strARestrictions(CInt(dr("RestrictionNumber")) - 1)
= Nothing
Else
strARestrictions(CInt(dr("RestrictionNumber")) - 1)
= dr("Value").ToString
End If
End If
Next
Dim dt As DataTable = cn.GetSchema(cmbTables.Text,
strARestrictions)
DataGridView1.DataSource = dt
End If
End Sub
End Class

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
I went as deep into SMO as I could and the yellow brick road has to lead to
the Column object but this task is really obscure. I am surprised after
drilling right into that SMO object that I am still not able to determine
how to even reference the column's Description property so I can get the
description text out of each column and put it into an ADO DataTable object.
 
which finally leads to another spelling: fn_listextendedproperty that
returns the text in the Description property so thank you Fred. Or should I
say SchemaMaster :-)
 
William is your code example another way to get the text of the Description
property that is returned by fn_listextendedproperty in SQL 2005?
 
HillBilly said:
I need to get the Description meta data from a SQL 2005 table. In other
words I'm looking for the space delineated text in the Description i.e.
First Name that is a description for the FirstName column. I don't know
how to find the Description meta data or put it into a TableAdapter.
Click to expand...

The query for sqlserver 2005:
SELECT o.Name AS ObjectName,
o.type AS ObjectType,
s.name AS SchemaOwner,
ep.name AS PropertyName,
ep.value AS PropertyValue,
c.name AS ColumnName,
c.colid AS Ordinal
FROM sys.objects o INNER JOIN sys.extended_properties ep
ON o.object_id = ep.major_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN syscolumns c
ON ep.minor_id = c.colid
AND ep.major_id = c.id
WHERE o.type IN ('V', 'U', 'P')
ORDER BY SchemaOwner,ObjectName, ObjectType, Ordinal

of course you should add additional where predicates to it to retrieve
only the meta-data for your tables.

'U' is table, 'V' is view, 'P' is proc. Run the query on your db to get
an understanding ofthe output. :)

FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Frans Bouma said:
The query for sqlserver 2005:
SELECT o.Name AS ObjectName,
o.type AS ObjectType,
s.name AS SchemaOwner,
ep.name AS PropertyName,
ep.value AS PropertyValue,
c.name AS ColumnName,
c.colid AS Ordinal
FROM sys.objects o INNER JOIN sys.extended_properties ep
ON o.object_id = ep.major_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN syscolumns c
ON ep.minor_id = c.colid
AND ep.major_id = c.id
WHERE o.type IN ('V', 'U', 'P')
ORDER BY SchemaOwner,ObjectName, ObjectType, Ordinal

of course you should add additional where predicates to it to retrieve
only the meta-data for your tables.

'U' is table, 'V' is view, 'P' is proc. Run the query on your db to get an
understanding ofthe output. :)

FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...

Hello Frans, long time no see in the same news article :-)

I executed that code but I don't understand how to modify it with
predicates to function with a specific table. I tried replacing
'U' with 'Test1TableName' and the entire response was empty. I'd
go read and study BOL if I knew what to look for to explicityly
understand your code; especially the U,V, and P parameters you infer.
presumably by reading sys.extended_properties documentation.


Last night I worked this out on a specific table...

--start: get Description property text of each column in named table
USE MyDatabaseName;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL,
'schema',
'dbo',
'table',
'MyTableName',
'column',
default);
GO
--end: get Description property text of each column in named table

So both code snippets get the text from the Description property so
now what? I have to determine how to return all of that text to
the calling page.

All I would understand conceptually at the moment is thinking I now
need to do the following:

* use T-SQL to isolate the Description text from the collection
of extended properties returned by fn_listextendedproperty
(how?)

* Aggregate and delineate the isolated text into a string
(know this already)

* Return the delineated string with an output parameter
(know this already)

// example delineated string
"First Name:,Middle Name:,Last Name:,Street Address:,..."

How's that sound?
 
HillBilly said:
I executed that code but I don't understand how to modify it with
predicates to function with a specific table.
Click to expand...

o.Type is the type of the object, e.g.: a proc, a view and a table.
WHICH table is in o.Name (ObjectName), and in which schema is in s.Name
(SchemaOwner).

So if you want to fetch only the extended properties of the table
'Customers' in the schema 'dbo' you should do:


SELECT o.Name AS ObjectName,
o.type AS ObjectType,
s.name AS SchemaOwner,
ep.name AS PropertyName,
ep.value AS PropertyValue,
c.name AS ColumnName,
c.colid AS Ordinal
FROM sys.objects o INNER JOIN sys.extended_properties ep
ON o.object_id = ep.major_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN syscolumns c
ON ep.minor_id = c.colid
AND ep.major_id = c.id
WHERE o.type IN ('V', 'U', 'P')
AND o.Name='Customers' AND s.Name='dbo'
ORDER BY SchemaOwner,ObjectName, ObjectType, Ordinal

the semi last line has the extra predicate. Of course,you can use an IN
clause as well to fetch only the extended properties of a set of schemas
and a set of tables.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
<snip />

Thank you for that clarification Frans. What do you think about my presumed
order of bulleted tasks (*) and how to get them done?
 
HillBilly said:
<snip />

Thank you for that clarification Frans. What do you think about my
presumed order of bulleted tasks (*) and how to get them done?
Click to expand...

I wouldn't do that on the DB side, but in client code. Resultset
manipulation is not something the DB is designed for, IMHO.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Frans Bouma said:
I wouldn't do that on the DB side, but in client code. Resultset
manipulation is not something the DB is designed for, IMHO.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...

No problem deferring to your sage advice but I have no direction or
methodology on how to proceed them. I have to get all of the descriptions
out of the table and bring them back to the calling method on the server as
I am attempting to automate the UI by using the descriptions as text
properties for LinkButtons in a DataGrid used to edit the data in thye
table. I'm astonished to be learning this hasn't been done as a common task
and apparently remains very obscure.
 
HillBilly said:
No problem deferring to your sage advice but I have no direction or
methodology on how to proceed them. I have to get all of the
descriptions out of the table and bring them back to the calling method
on the server as I am attempting to automate the UI by using the
descriptions as text properties for LinkButtons in a DataGrid used to
edit the data in thye table. I'm astonished to be learning this hasn't
been done as a common task and apparently remains very obscure.
Click to expand...

The extended properties are just name-value pairs. Why not adding them
to a dictionary on the client while processing the resultset
sequentially and use the contents of the dictionary easy access what you
need to display?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Frans Bouma said:
The extended properties are just name-value pairs. Why not adding them to
a dictionary on the client while processing the resultset sequentially and
use the contents of the dictionary easy access what you need to display?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...

Sure, I do okay with JavaScript but how do I get the collection from the
table
to the client? I'm very weak on T-SQL.
 
Back
Top