INFORMATION SCHEMA. COLUMNS
– Displays columns accessable to the current user
CREATE VIEW INFORMATION_SCHEMA.COLUMNS
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
ColumnProperty(c.object_id, c.name, ‘ordinal’) AS ORDINAL_POSITION,
convert(nvarchar(4000),
object_definition(c.default_object_id)) AS COLUMN_DEFAULT,
convert(varchar(3), CASE c.is_nullable
WHEN 1 THEN ‘YES’ ELSE ‘NO’ END) AS IS_NULLABLE,
type_name(c.system_type_id) AS DATA_TYPE,
ColumnProperty(c.object_id, c.name, ‘charmaxlen’) AS CHARACTER_MAXIMUM_LENGTH,
ColumnProperty(c.object_id, c.name, ‘octetmaxlen’) AS CHARACTER_OCTET_LENGTH,
convert(tinyint, CASE — int/decimal/numeric/real/float/money
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
END) AS NUMERIC_PRECISION,
convert(smallint, CASE — int/money/decimal/numeric
WHEN c.system_type_id IN (48, 52, 56, 60, 106, 108, 122, 127) THEN 10
WHEN c.system_type_id IN (59, 62) THEN 2 END) AS NUMERIC_PRECISION_RADIX, — real/float
convert(int, CASE — datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN NULL
ELSE odbcscale(c.system_type_id, c.scale) END) AS NUMERIC_SCALE,
convert(smallint, CASE — datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN 3 END) AS DATETIME_PRECISION,
convert(sysname, null) AS CHARACTER_SET_CATALOG,
convert(sysname, null) AS CHARACTER_SET_SCHEMA,
convert(sysname, CASE
WHEN c.system_type_id IN (35, 167, 175) — char/varchar/text
THEN CollationProperty(c.collation_name, ’sqlcharsetname’)
WHEN c.system_type_id IN (99, 231, 239) — nchar/nvarchar/ntext
THEN N’UNICODE’
END) AS CHARACTER_SET_NAME,
convert(sysname, null) AS COLLATION_CATALOG,
convert(sysname, null) AS COLLATION_SCHEMA,
c.collation_name AS COLLATION_NAME,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN db_name() END) AS DOMAIN_CATALOG,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN schema_name(o.schema_id)
END) AS DOMAIN_SCHEMA,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN type_name(c.user_type_id)
END) AS DOMAIN_NAME
FROM
sys.objects o JOIN sys.columns c ON c.object_id = o.object_id
WHERE
o.type IN (’U', ‘V’)
———-