getting database metadata information, please help

[Mario]

Please, could anyone tell me how to get information from which attribute
of which table is the attribute from the view derived(it could also be a
complex expression, not just attribute)through querying system tables or
views(INFORMATION_SCHEMA, preferably if possible, because it's standard)

i'm using MSSQL2005.

Thank you!

[John Bell]

Hi Mario

The information_schema views don't hold all the information you need for
instance
INFORMATION_SCHEMA.VIEWS will hold upto 4000 characters of the view
definition (therefore may be incomplete!) and
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE only returns information about columns
and not computed columns e.g.

CREATE DATABASE TESTDB
GO

USE TESTDB
GO

CREATE TABLE t1 ( id1 int not null, id2 int not null )
GO

CREATE VIEW v1 AS
SELECT id1, id2, id1*id2 as IDPRODUCT
FROM T1
GO

SELECT * FROM INFORMATION_SCHEMA.VIEWS
GO

-- Only returns 2 rows
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
GO

USE MASTER
GO

DROP DATABASE TESTDB
GO

It is not clear why you need this, the information would be in your
modelling tool/database diagrams

John

[m]

Thanks for reply!

i'm programming a code generator, and some capabilities would be reverse
engineering a database(extracting metadata) and also generating business
objects(CSLA based) and stored procedures to support their(BO's)
functionality. I can get all the info about tables, but for views i
couldn't find a way to extract that information...

i've also come up with the same conclusion about inability to do this
through INFORMATION_SCHEMA, but it is logical that it should be possible
through some other means...

[John Bell]

Hi

The column information in INFORMATION_SCHEMA.VIEW_COLUMN_USAGE is fine
unless it is computed so most views may be ok.

Those columns that in syscolumns but not
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE must something other than columns in
tables.

I can only think of looking at the view definition and parsing it to get how
is is calculated.

John

[m]

Hi!

there's just one more question...(having in mind that i'm more
experienced in other DBMSs)

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE gives me the columns of tables
which i used in my views,
and INFORMATION_SCHEMA.COLUMNS gives me the columns in views, and then i
can nicely figure out by name which is which, but what if i use alias
names for view columns?
and more, if i use columns from more than one table in view then in
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE i get not just the attributes i
chose, but also all attributes that were used for joins...

thanks!

[John Bell]

Hi

The column name is the underlying column in
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

CREATE VIEW v1 AS

SELECT id1 AS Col1, id2, id1*id2 as IDPRODUCT

FROM T1

GO

-- returns Id1 as column_name

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

GO

You can join on the TABLE_NAME and TABLE_SCHEMA to get a fully qualified
match on column, so if you have two columns with the same name in two
different tables only the correct one will match.

John

"m" <m...@g.ht> wrote in message news:frl43m$a9r$1...@ss408.t-com.hr...

[m]

i was just today examining one of leading DB generators, and their
principle was parsing view definition, so that's what i'm going to do...
hopefully, that missing info will be reachable in future versions of
MSSQL (if it isn't already in 2008 version) without parsing.

thanks!

John Bell wrote:
> Hi
>

[Knowledgy]

query the syscomments system table and filter on the text column where text
like '%object%'

--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/

Atlanta's Business Intelligence and Data Warehouse Experts

"Mario" <mario.brci...@fer.hr> wrote in message
news:fre1po$6et$1...@ss408.t-com.hr...

[David Portas]

"Mario" <mario.brci...@fer.hr> wrote in message
news:fre1po$6et$1...@ss408.t-com.hr...

sys.sql_dependencies

--
David Portas