SELECT
Cols.TABLE_SCHEMA,
Cols.TABLE_NAME,
Cols.COLUMN_NAME,
Cols.ORDINAL_POSITION,
Cols.DATA_TYPE,
Cols.NUMERIC_PRECISION,
Cols.NUMERIC_SCALE,
Cols.IS_NULLABLE,
Cols.CHARACTER_MAXIMUM_LENGTH,
COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
( SELECT COUNT(KCU.COLUMN_NAME)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE KCU.TABLE_NAME = Cols.TABLE_NAME
AND KCU.COLUMN_NAME = Cols.COLUMN_NAME
) AS IsIndex
FROM [INFORMATION_SCHEMA].[COLUMNS] Cols
where Table_Name in ( 'view1' , 'view2' )
--AND Cols.TABLE_SCHEMA = ??
ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.table_schema , Cols.ORDINAL_POSITION
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!