Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Шозатип?
Guest
Тип данных поля я могу получить из INFORMATION_SCHEMA.COLUMNS. Но там данные разделены - отдельно имя типа, отдельно макс.длина, отдельно точность, итд - а как бы мне получить тип в том виде, в каком он указывается в CREATE TABLE? Например, для поля, у которого

data_typenumeric_precisionnumeric_scale
decimal248

получить 'decimal(24,8)'? Или придется самому писать функцию, в которой анализировать - вот этот тип символьный и имеет макс.длину, значит склеиваем имя типа и макс.длину в скобках (а если макс.длина -1, то вместо -1 пишем max), а вот этот числовой, и имеет точность и масштаб - значит, склеиваем имя типа, и точность с масштабом через запятую в скобках, и т.п.
4 апр 14, 08:29    [15831032]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Кавказ-сила
Member

Откуда: Москва
Сообщений: 261
http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table
4 апр 14, 09:28    [15831181]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Шозатип?
Guest
Кавказ-сила
http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table

Спасибо. Т.е. встроенных средств нет - нужна своя функция или запрос.
Я первый запрос по ссылке немного модифицировал так:
;with cte as (
  select
    c.name column_name,
    columnproperty(c.object_id, c.name, 'ordinal') ordinal_position,
    type_name(c.user_type_id) data_type,
    columnproperty(c.object_id, c.name, 'charmaxlen') character_maximum_length,
    convert(tinyint,
      case
        when c.system_type_id in (48, 52, 56, 59, 60, 62, 106, 108, 122, 127)
        then c.precision
      end) numeric_precision,
    convert(int,
      case
        when c.system_type_id in (58, 61) then null
        else odbcscale(c.system_type_id, c.scale)
      end) numeric_scale
  from sys.columns c 
  where c.object_id = object_id('dbo.test')
)
select column_name,
  data_type +
  case 
    when data_type in ('sql_variant','text','ntext','xml','image','hierarchyid','geometry','geography','sysname')
      then ''
    when data_type in ('decimal','numeric')
      then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
    else coalesce('('+case when character_maximum_length = -1 then 'max' else cast(character_maximum_length as varchar) end +')','')
  end col_definition,
  ordinal_position 
from cte
order by ordinal_position

- в исходном были косяки с обработкой типов numeric, sysname, hierarchyid, geometry, geography. Проверял вот на такой таблице:
create table dbo.test (
  [int] int,
  [decimal(24,8)] decimal(24,8),
  [numeric(24,8)] numeric(24,8),
  [float] float,
  [char(10)] char(10),
  [nchar(10)] nchar(10),
  [varchar(100)] varchar(100),
  [nvarchar(100)] nvarchar(100),
  [varchar(max)] varchar(max),
  [nvarchar(max)] nvarchar(max),
  [binary(2)] binary(2),
  [varbinary(2)] varbinary(2),
  [varbinary(max)] varbinary(max),
  [image] image,
  [text] text,
  [ntext] ntext,
  [uniqueidentifier] uniqueidentifier,
  [sysname] sysname,
  [xml] xml,
  [sql_variant] sql_variant,
  [hierarchyid] hierarchyid,
  [geometry] geometry,
  [geography] geography,
  [timestamp] timestamp,
  [bit] bit,
  [money] money,
  [smallmoney] smallmoney,
  [datetime] datetime,
  [datetime2] datetime2,
  [date] date,
  [smalldatetime] smalldatetime,
  [datetimeoffset] datetimeoffset,
  [real] real
)

- вроде бы выдает всё так как надо.
4 апр 14, 10:23    [15831491]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Шозатип?,

а если DATETIMEOFFSET(5)?
А если имя поля содержит квадратные скобки?
Произвольный UDT я не смотрел. Правильно обработается?
4 апр 14, 10:38    [15831589]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Шозатип?
Guest
iap,

имя поля для меня - дело пятое, оно в выборке просто для информации, главное - тип.
Переделал вот так:
;with cte as (
  select
    c.name column_name,
    columnproperty(c.object_id, c.name, 'ordinal') ordinal_position,
    type_name(c.user_type_id) data_type,
    columnproperty(c.object_id, c.name, 'charmaxlen') character_maximum_length,
    convert(tinyint,
      case
        when c.system_type_id in (48, 52, 56, 59, 60, 62, 106, 108, 122, 127)
        then c.precision
      end) numeric_precision,
    convert(int,
      case
        when c.system_type_id in (40, 41, 42, 43, 58, 61) then null
        else odbcscale(c.system_type_id, c.scale)
      end) numeric_scale,
    convert(smallint,
      case
        when c.system_type_id in (40, 41, 42, 43, 58, 61)
        then odbcscale(c.system_type_id, c.scale)
      end) datetime_precision,
    case when user_type_id>256 then 1 else 0 end is_udt
  from sys.columns c 
  where c.object_id = object_id('dbo.test')
)
select column_name,
  data_type +
  case 
    when
      data_type in ('sql_variant','text','ntext','xml','image','hierarchyid','geometry','geography','sysname')
      or is_udt=1
      then ''
    when data_type in ('decimal','numeric')
      then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
    when data_type='datetimeoffset' and datetime_precision<7
      then '('+cast(datetime_precision as varchar)+')'
    else coalesce('('+case when character_maximum_length = -1 then 'max' else cast(character_maximum_length as varchar) end +')','')
  end col_definition,
  ordinal_position 
from cte
order by ordinal_position

и проверил вот так:
create type SSN from varchar(11) not null;
create table dbo.test (
  [int] int,
  [decimal(24,8)] decimal(24,8),
  [numeric(24,8)] numeric(24,8),
  [float] float,
  [char(10)] char(10),
  [nchar(10)] nchar(10),
  [varchar(100)] varchar(100),
  [nvarchar(100)] nvarchar(100),
  [varchar(max)] varchar(max),
  [nvarchar(max)] nvarchar(max),
  [binary(2)] binary(2),
  [varbinary(2)] varbinary(2),
  [varbinary(max)] varbinary(max),
  [image] image,
  [text] text,
  [ntext] ntext,
  [uniqueidentifier] uniqueidentifier,
  [sysname] sysname,
  [xml] xml,
  [sql_variant] sql_variant,
  [hierarchyid] hierarchyid,
  [geometry] geometry,
  [geography] geography,
  [timestamp] timestamp,
  [bit] bit,
  [money] money,
  [smallmoney] smallmoney,
  [datetime] datetime,
  [datetime2] datetime2,
  [date] date,
  [smalldatetime] smalldatetime,
  [datetimeoffset] datetimeoffset,
  [datetimeoffset(5)] datetimeoffset(5),
  [real] real,
  [SSN] SSN
)

- теперь ситуация с datetimeoffset и UDT обрабатывается нормально.
4 апр 14, 11:22    [15831877]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Шозатип?,

А еще, например, может быть float(30).
4 апр 14, 11:31    [15831942]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Шозатип?,

я просто намекал на функцию QUOTENAME.
В скрипте по исходной ссылке пишут '[' + ... + ']'
Это очень безграмотно
4 апр 14, 11:32    [15831947]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Шозатип?
Guest
invm
Шозатип?,

А еще, например, может быть float(30).

Не может. Точнее, может, но это эквивалентно просто float, и при извлечении DDL тоже будет просто float:
http://technet.microsoft.com/en-us/library/ms173773.aspx
SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.

А если указать float(20), то при извлечении DDL это будет заменено типом real.
iap
я просто намекал на функцию QUOTENAME.
В скрипте по исходной ссылке пишут '[' + ... + ']'
Это очень безграмотно

А, ну да. Не заметил (поскольку как писал выше, мне это неважно).
4 апр 14, 12:09    [15832198]     Ответить | Цитировать Сообщить модератору
 Re: Как тип данных поля получить в том виде, в каком он в скрипте таблицы?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Можете тему посмотреть, посравнивать: 5924911
4 апр 14, 23:18    [15835707]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить