How to generate a CREATE TABLE script for an existing table

[neru]

На скуле бы вас сейчас кое-чем закидали за то, что версию сервера не указываете ;-)

Такое поведение весьма печально, поскольку для некоторых сценариев, бывает полезно получить скриптовое описание таблицы.

Приведите, пожалуйста, пример такого сценария.

[AlanDenton]

что версию сервера не указываете ;-)

Вся информация актуальна для SQL Server 2005 и выше.

Приведите, пожалуйста, пример такого сценария.

Впервые я использовал данных подход для трекинга изменений среди таблиц. Недавно возникла задача воссоздать функционал SQL Prompt — просмотр дефинишина объекта.

[mayorovp]

OBJECT_SCHEMA_NAME не существует в SQL Server 2005

[AlanDenton]

Не хотелось бы спорить, но есть:

msdn.microsoft.com/en-us/library/Bb326599%28v=sql.90%29.aspx

[mayorovp]

Хм, фантастика какая-то…

[AlanDenton]

Возможно у Вас выставлен режим совместимости с SQL Server 2000?

[mayorovp]

Если вспомнить теорию, то PRIMARY KEY — это кластерный индекс и ограничение Unique.

На уровне метаданных, SQL Server для всех кластерных индексов задает index_id равный 1, поэтому можно сделать выборку из sys.indexes фильтруя по index_id = 1 либо is_primary_key = 1.

Хотелось бы напомнить, что могут существовать и «аномальные» таблицы:
— без кластерного индекса, но с первичным ключом,
— без первичного ключа, но с кластерным индексом,
— и даже с двумя разными индексами, один из которых является кластерным, а второй — первичным ключом.

Также ограничения CHECK и DEFAULT тоже могут иметь свои имена, что так же бывает важным (кстати, что-то я не увидел в статье обработки произвольных индексов и этих самых CHECK CONSTRAINTs).

[AlanDenton]

Хотелось бы напомнить, что могут существовать и «аномальные» таблицы:

Все это подпадает под условие is_primary_key = 1

Также ограничения CHECK и DEFAULT тоже могут иметь свои имена

При редактирование имени PK констрейнта, SQL Server автоматически переименовывает и кластерный индекс тоже.

что-то я не увидел в статье обработки произвольных индексов и этих самых CHECK CONSTRAINTs

Предполагалось осветить эти вопросы в продолжении.

[mayorovp]

Все это подпадает под условие is_primary_key = 1

Не попадает. Запрос, который вы в итоге сгенерируете, создаст другую таблицу, отличную от исходной по структуре.

Также ограничения CHECK и DEFAULT тоже могут иметь свои имена

При редактирование имени PK констрейнта, SQL Server автоматически переименовывает и кластерный индекс тоже.

О чем вы вообще? Я о CK и DF, вы вдруг о PK…

Если не понятно, то вот табличка, на которой можете проверить свой скрипт:

create table test (
	id1 int not null identity (100, 12),
	id2 int not null constraint pk_test primary key nonclustered,
	id3 int not null constraint df_test_id3 default 42 constraint ck_test_id3 check ( id3 <> 7),
	id4 int not null constraint ix_test unique clustered,
);

Вот, кстати, что у меня получилось после запуска вашего скрипта:

CREATE TABLE dbo.test (
       [id1] [INT] NOT NULL IDENTITY(100,12)
     , [id2] [INT] NOT NULL
     , [id3] [INT] NOT NULL DEFAULT ((42))
     , [id4] [INT] NOT NULL
      , CONSTRAINT [pk_test] PRIMARY KEY ([id2]) );

Итог: на id4 вообще пропал индекс, df_test превратился в DF__test__id3__069ABC37 (могут возникнуть проблемы, если очередной скрипт миграции попытается убрать ограничение default), ck_test вообще исчезло.

[AlanDenton]

О чем вы вообще? Я о CK и DF, вы вдруг о PK…

Спасибо за замечание. Немного поправил скрипт. Для Вашего примера мы получаем следующий скрипт:

CREATE TABLE [dbo].[test]
(
      [id1] [INT] NOT NULL IDENTITY(100,12)
    , [id2] [INT] NOT NULL
    , [id3] [INT] NOT NULL CONSTRAINT [df_test_id3] DEFAULT ((42)) CONSTRAINT [ck_test_id3] CHECK ([id3]<>(7))
    , [id4] [INT] NOT NULL
    , CONSTRAINT [pk_test] PRIMARY KEY NONCLUSTERED ([id2])
);

PS. Генерацию остальных индексов планирую добавить в продолжении.