CREATE VIEW dbo.vColumnInfo
AS
SELECT
tbl.name AS [Table_Name],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
CAST(ISNULL(cik.index_column_id , 0) AS BIT) AS [InPrimaryKey],
CAST(ISNULL((
SELECT TOP 1
1
FROM
sys.foreign_key_columns AS colfk
WHERE
colfk.parent_column_id = clmns.column_id
AND colfk.parent_object_id = clmns.object_id
) , 0) AS BIT) AS [IsForeignKey],
QUOTENAME(clmns.name) AS [Column_Name],
clmns.object_id AS object_id,
clmns.column_id AS column_id,
clmns.is_computed AS Is_Computed,
ISNULL(cc.definition , N'') AS computed_column_definition,
QUOTENAME(usrt.name)
+ CASE usrt.name
WHEN 'binary'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'char'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'decimal'
THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20))
+ ') '
WHEN 'nchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'numeric'
THEN ' (' + CAST(clmns.precision AS VARCHAR(20)) + ',' + CAST(clmns.max_length AS VARCHAR(20))
+ ') '
WHEN 'nvarchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'varbinary'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'varchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'xml'
THEN CASE WHEN ISNULL(xscclmns.name , N'') = ''
THEN ''
ELSE ' (CONTENT ' + QUOTENAME(s2clmns.name)
+ '.' + QUOTENAME(xscclmns.name)
+ ') '
END
ELSE ''
END AS Data_Type,
clmns.is_identity AS is_identity,
CASE clmns.is_identity
WHEN 1
THEN ' IDENTITY (' + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10))
+ ','
+ CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10))
+ ') '
ELSE ' '
END AS [Identity],
clmns.is_nullable AS is_nullable,
CASE clmns.is_nullable
WHEN 1 THEN ' NULL '
ELSE ' NOT NULL '
END AS [Nullable],
CASE WHEN cstr.name <> ''
THEN ' CONSTRAINT ' + QUOTENAME(cstr.name)
+ ' DEFAULT ' + cstr.definition + ','
ELSE ''
END AS [Constraint],
QUOTENAME(clmns.name) + ' '
+ CASE clmns.is_computed
WHEN 1
THEN ' AS ' + ISNULL(cc.definition , N'') + ','
ELSE QUOTENAME(usrt.name)
+ CASE usrt.name
WHEN 'binary'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'char'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'decimal'
THEN ' (' + CAST(clmns.precision AS VARCHAR(20))
+ ','
+ CAST(clmns.max_length AS VARCHAR(20))
+ ') '
WHEN 'nchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'numeric'
THEN ' (' + CAST(clmns.precision AS VARCHAR(20))
+ ','
+ CAST(clmns.max_length AS VARCHAR(20))
+ ') '
WHEN 'nvarchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'varbinary'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'varchar'
THEN CASE clmns.max_length
WHEN -1 THEN '(max) '
ELSE ' (' + CAST(clmns.max_length / 2 AS VARCHAR(20))
+ ') '
END
WHEN 'xml'
THEN CASE WHEN ISNULL(xscclmns.name , N'') = ''
THEN ''
ELSE ' (CONTENT '
+ QUOTENAME(s2clmns.name)
+ '.'
+ QUOTENAME(xscclmns.name)
+ ') '
END
ELSE ''
END
+ CASE clmns.is_identity
WHEN 1
THEN ' IDENTITY (' + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10))
+ ','
+ CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10))
+ ') '
ELSE ' '
END + CASE clmns.is_nullable
WHEN 1 THEN ' NULL '
ELSE ' NOT NULL '
END
+ CASE WHEN cstr.name <> ''
THEN ' CONSTRAINT '
+ QUOTENAME(cstr.name)
+ ' DEFAULT ' + cstr.definition
+ ','
ELSE ','
END
END AS [Column_Text]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns
ON clmns.object_id = tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik
ON ik.object_id = clmns.object_id
AND 1 = ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik
ON cik.index_id = ik.index_id
AND cik.column_id = clmns.column_id
AND cik.object_id = clmns.object_id
AND 0 = cik.is_included_column
LEFT OUTER JOIN sys.computed_columns AS cc
ON cc.object_id = clmns.object_id
AND cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt
ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = clmns.system_type_id
AND baset.user_type_id = baset.system_type_id
LEFT OUTER JOIN sys.schemas AS sclmns
ON sclmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.identity_columns AS ic
ON ic.object_id = clmns.object_id
AND ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d
ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.objects AS r
ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns
ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns
ON s2clmns.schema_id = xscclmns.schema_id
LEFT OUTER JOIN sys.default_constraints AS cstr
ON cstr.object_id = clmns.default_object_id
