|
Tuesday, 11 October 2011 10:51 |
CREATE VIEW dbo.vw_XL_TableColumns
AS
SELECT TOP (100) PERCENT tabs.name AS TableName, col.name AS ColumnName, coltype.name AS TypeName, col.max_length AS MaxLength, col.precision, col.scale,
CASE WHEN col.name = pkCol.COLUMN_NAME THEN 'Y' ELSE 'N' END AS IsPrimaryKey, CASE WHEN col.is_nullable = 1 THEN 'Y' ELSE 'N' END AS IsNullable,
CASE WHEN col.is_identity = 1 THEN 'Y' ELSE 'N' END AS IsIdentity, CASE WHEN col.is_computed = 1 THEN 'Y' ELSE 'N' END AS IsComputed,
col.column_id AS ColumnOrder
FROM sys.columns AS col INNER JOIN
sys.tables AS tabs ON col.object_id = tabs.object_id INNER JOIN
sys.systypes AS coltype ON coltype.xtype = col.system_type_id LEFT OUTER JOIN
(SELECT c.COLUMN_NAME, c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pk INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c ON pk.TABLE_NAME = c.TABLE_NAME AND
pk.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE (pk.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS pkCol ON pkCol.TABLE_NAME = tabs.name AND pkCol.COLUMN_NAME = col.name
ORDER BY TableName, ColumnOrder Read more: |
