How to get the metadata of a table

As title, such as the PK, data type of each column, etc.

Thanks,

Ricky.

The "standard" way is to use the information_schema views. It is the best way in 2000:

use tempdb

go

create table viewMetadata

(

column1 varchar(10) primary key,

column2 int unique,

check (column2 > column1)

)

go

select *

from information_schema.tables

where table_name = 'viewMetadata'

select *

from information_schema.columns

where table_name = 'viewMetadata'

select *

from information_schema.table_constraints

where table_name = 'viewMetadata'

etc

And there are more. Look up information_schema in BOL. In 2005, you can also use the system catalog views. They are not as convienient as the information_schema views, but they are a lot more powerful. For example:

select *

from sys.tables

where name = 'viewMetadata'

select *

from sys.columns

where object_id = object_id('viewMetadata')

select *

from sys.check_constraints

where parent_object_id = object_id('viewMetadata')

select *

from sys.key_constraints

where parent_object_id = object_id('viewMetadata')

It is really a big topic, I would suggest you look in BOL for Object Catalog Views.

|||

Hi, Louis. Thanks for your reply first.

However, I got empty results following your instruction like:

select *

from sys.tables

where name = 'AdventureWorks.Person.Address'

By the way, if I want to detect the primary key of 'AdventureWorks.Person.Address', what SQL statement should I issue to SQL 2005?

Regards,

Ricky.

|||

The name of the table is just Address, the schema is Person, and the database is AdventureWorks:

use AdventureWorks

go

select *

from sys.tables

where object_id = object_id('Person.Address')

/* alternative

where name = 'Address'

and schema_id = schema_id('Person')

*/

select *

from sys.key_constraints

where parent_object_id = object_id('Person.Address')

and type = 'PK'

|||

Thanks a lot, Louis.

Ricky.