Metadata Detail

Hi all
I want to get some metadata information for my tables
Some column properties I can get using System tabes and Information Schema
but I can not get some information as below
I want to get information of column properties Identity Seed, Identity
Increment, Is Rowguid , Formula value, Description for my any column which
this property applicable.
Any help will be highly appreciated
Thanks> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT. For
"Description" use system function fn_listextendedproperty and for "Formula
value" check system table syscomments.
Example:
use northwind
go
create table dbo.t (
colA int not null identity,
colB uniqueidentifier ROWGUIDCOL not null default(newid()),
colC as power(2, colA),
colD as colA % 10
)
go
select
ordinal_position,
column_name,
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else 'No'
end as [IsIdentity],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_seed(table_name)) else '' end as [ident_seed],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_incr(table_name)) else '' end as [ident_incr],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_current(table_name)) else '' end as [ident_current],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else 'No
'
end as [IsRowGuidCol],
coalesce(sc.[text], '') as [Formula]
from
information_schema.columns as c
left join
syscomments as sc
on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
sc.[id] and sc.number = c.ordinal_position
where
table_name = 't'
order by
ordinal_position
go
drop table t
go
AMB
"AM" wrote:

> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information Schem
a
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>
>|||Thanks
It helps me a lot
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
which
> Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT.
For
> "Description" use system function fn_listextendedproperty and for "Formula
> value" check system table syscomments.
> Example:
> use northwind
> go
> create table dbo.t (
> colA int not null identity,
> colB uniqueidentifier ROWGUIDCOL not null default(newid()),
> colC as power(2, colA),
> colD as colA % 10
> )
> go
> select
> ordinal_position,
> column_name,
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else
'No'
> end as [IsIdentity],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_seed(table_name)) else '' end as [ident_seed],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_incr(table_name)) else '' end as [ident_incr],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_current(table_name)) else '' end as [ident_current],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else
'No'
> end as [IsRowGuidCol],
> coalesce(sc.[text], '') as [Formula]
> from
> information_schema.columns as c
> left join
> syscomments as sc
> on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
> sc.[id] and sc.number = c.ordinal_position
> where
> table_name = 't'
> order by
> ordinal_position
> go
> drop table t
> go
>
> AMB
>
> "AM" wrote:
>
Schema
which|||See if this helps:
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"AM" wrote:

> Thanks
> It helps me a lot
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
> which
> For
> 'No'
> 'No'
> Schema
> which
>
>|||Also check out following link for " Schema: How do I show the description
property of a column? ":
http://www.aspfaq.com/show.asp?id=2244
"AM" <anonymous@.examnotes.net> wrote in message
news:%23WseefgOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information
> Schema
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column
> which
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>