/*
Metadaten: Information über SQL Tabellen u. Felder
*/
--V1 (einfach)
Select ORDINAL_POSITION, TABLE_SCHEMA, TABLE_NAME
, IS_NULLABLE, COLUMN_DEFAULT
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
and TABLE_NAME='Errors'
Order by ORDINAL_POSITION
--V2 (ausführlicher)
Select c.ORDINAL_POSITION, c.TABLE_SCHEMA, c.TABLE_NAME, c.IS_NULLABLE, Coalesce(c.COLUMN_DEFAULT,'') as COLUMN_DEFAULT
,Case when pk.COLUMN_NAME is NULL then '' else 'PK' end as HAS_PRIMARY_KEY
--,c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_PRECISION_RADIX, c.NUMERIC_SCALE, c.DATETIME_PRECISION
,CASE
WHEN c.[data_type] IN (
'tinyint'
,'smallint'
,'int'
,'bigint'
,'float'
)
THEN c.[data_type]
WHEN c.[data_type] IN ('bit')
THEN c.[data_type]
WHEN c.[data_type] IN ('decimal')
THEN c.[data_type] + '(' + CAST(c.[numeric_precision] AS NVARCHAR(10)) + ',' + CAST(c.[numeric_scale] AS NVARCHAR(10)) + ')'
WHEN c.[data_type] IN (
'smallmoney'
,'money'
)
THEN c.[data_type]
WHEN c.[data_type] IN (
'char'
,'nchar'
,'varchar'
,'nvarchar'
)
AND c.[character_maximum_length] < 0
THEN c.[data_type] + '(MAX)'
WHEN c.[data_type] IN (
'char'
,'nchar'
,'varchar'
,'nvarchar'
)
AND c.[character_maximum_length] >= 0
THEN c.[data_type] + '(' + CAST(c.[character_maximum_length] AS NVARCHAR(10)) + ')'
WHEN c.[data_type] IN (
'date'
,'time'
,'datetime'
)
THEN c.[data_type]
WHEN c.[data_type] IN ('datetime2')
THEN c.[data_type] + '(7)'
WHEN c.[data_type] IN ('uniqueidentifier')
THEN c.[data_type]
WHEN c.[data_type] IN ('xml')
THEN c.[data_type]
WHEN c.[data_type] IN ('geography')
THEN c.[data_type]
WHEN c.[data_type] IN ('hierarchyid')
THEN c.[data_type]
ELSE c.[data_type]
END AS [data_type_explicit]
from INFORMATION_SCHEMA.COLUMNS as c
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as pk
ON c.TABLE_CATALOG=pk.TABLE_CATALOG and c.TABLE_SCHEMA=pk.TABLE_SCHEMA and c.TABLE_NAME=pk.TABLE_NAME and c.COLUMN_NAME=pk.COLUMN_NAME
where c.TABLE_SCHEMA='dbo'
and c.TABLE_NAME='Errors'
Order by c.ORDINAL_POSITION