need to get information from Information_schema

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need to get information from Information_schema

Author

Topic

dexterm
Starting Member

4 Posts

Posted - 2007-08-18 : 10:24:03

I am querying the Information Schema to return table and column information, like Name, DataType, etc. I am using a query like this:

select *
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'ClientActivities'

This query is returning the majority of the info that I need. However, it is not returning the following information:

- Whether the column is part of the Primary Key
- Whether the column is an identity column

If someone could send me a query that would return that data, I would appreciate it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 10:26:48

for PK use see INFORMATION_SCHEMA.KEY_COLUMN_USAGE
for identity see
COLUMNPROPERTY(OBJECT_ID(KCU.TABLE_NAME), KCU.COLUMN_NAME, 'IsIdentity') AS IsIdentity

where KCU = INFORMATION_SCHEMA.KEY_COLUMN_USAGE

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources