Primary Key in View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableif you want to know about the metadata in SQL Server Server like Constraints / Tables etc. you can use the INFORMATION_Schema Views, in this case you would query the INFORMATION_SCHEMA.Constraints view.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk