SELECT
Cols.TABLE_SCHEMA as 'TableSchema',
Cols.TABLE_NAME as 'TableName',
Cols.COLUMN_NAME as 'ColName',
Cols.ORDINAL_POSITION as 'OrdinalPosition',
Cols.DATA_TYPE as 'DataType',
Cols.NUMERIC_PRECISION as 'NumericPrecision',
Cols.NUMERIC_SCALE as 'NumericScale',
Cols.IS_NULLABLE as 'IsNullable',
Cols.CHARACTER_MAXIMUM_LENGTH as 'CharMaxLength' ,
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
ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.ORDINAL_POSITION
Posts
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 !!!!