Montag, 27. Februar 2017

MS SQL Table with Default Values

WITH colums as
(SELECT o.name              AS TABLE_NAME,
       c.column_id          AS COLUMN_ID,
       c.name               AS COLUMN_NAME,
       o.type_desc          AS TYPE_DESC,
       t.name               AS DATA_TYPE,
       c.max_length         AS MAX_LENGTH,
       c.[precision]        AS [PRECISION],
       c.scale SCALE,
       CASE c.is_nullable
           WHEN 1 THEN 'NOT NULL'
           ELSE ''
       END                  AS IS_NULLABLE,
       c.is_identity        AS IS_IDENTITY,
       c.collation_name     AS COLLATION_NAME,
       CASE
           WHEN r.[definition] LIKE '(''%'')' THEN substring(r.[definition],3,LEN(r.[definition])-4)
           WHEN r.[definition] LIKE '((%))' THEN substring(r.[definition],3,LEN(r.[definition])-4)
           ELSE 'unknown pattern'
       END AS [default_value]
FROM sys.columns c
     JOIN sys.objects o ON c.object_id = o.object_id
     JOIN sys.types t ON t.system_type_id = c.system_type_id
     LEFT JOIN sys.default_constraints r ON c.default_object_id = r.object_id)
     select * from colums where TABLE_NAME = 'asdf'

Keine Kommentare:

Kommentar veröffentlichen