Soru Sorgu ile mevcut tablolar için SQL Create Scripts oluşturun


Elde edebilmem lazım SQL Server 2008 içindeki mevcut tablolar için komut dosyaları oluşturma. Bunu bir şekilde sys.tables'i sorgulayarak yapabilirim, ancak bu bana CREATE betiği verisini geri göndermiyor.


44
2018-04-01 17:51


Menşei


Sadece CREATE TABLE veya diğer tüm bagajlar, örneğin endeksler, ilişkiler, kontrol kısıtlamaları, tetikleyiciler (Ateşleme emri korunmuş halde!)...? İlişkiler eklemeye başlar başlamaz yaratılış sırası kritik hale gelir. - HABO


Cevaplar:


Bu sizin için yararlı olabilir. Bu betik, herhangi bir tablo için dizinler, FK'ler, PK ve ortak yapı oluşturur.

Örneğin -

DDL:

CREATE TABLE [dbo].[WorkOut](
    [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL,
    [TimeSheetDate] [datetime] NOT NULL,
    [DateOut] [datetime] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [IsMainWorkPlace] [bit] NOT NULL,
    [DepartmentUID] [uniqueidentifier] NOT NULL,
    [WorkPlaceUID] [uniqueidentifier] NULL,
    [TeamUID] [uniqueidentifier] NULL,
    [WorkShiftCD] [nvarchar](10) NULL,
    [WorkHours] [real] NULL,
    [AbsenceCode] [varchar](25) NULL,
    [PaymentType] [char](2) NULL,
    [CategoryID] [int] NULL,
    [Year]  AS (datepart(year,[TimeSheetDate])),
 CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED 
(
    [WorkOutID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[WorkOut] ADD  
CONSTRAINT [DF__WorkOut__IsMainW__2C1E8537]  DEFAULT ((1)) FOR [IsMainWorkPlace]

ALTER TABLE [dbo].[WorkOut]  WITH CHECK ADD  CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])

ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]

Sorgu:

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.WorkOut'

DECLARE 
      @object_name SYSNAME
    , @object_id INT

SELECT 
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS 
(
    SELECT 
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS 
(
     SELECT 
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM sys.foreign_key_columns k WITH (NOWAIT)
    JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
    JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' + 
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition] 
            ELSE UPPER(tp.name) + 
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                     WHEN tp.name = 'decimal' 
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END 
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM sys.index_columns ic WITH (NOWAIT)
                         JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id 
                             AND ic.index_id = k.unique_index_id     
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id 
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + @object_name + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT ', [' + k.cname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
              + STUFF((
                SELECT ', [' + k.rcname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
        FROM sys.foreign_keys fk WITH (NOWAIT)
        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
    + ISNULL(((SELECT
         CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END 
                + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
                STUFF((
                SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'  
                + ISNULL(CHAR(13) + 'INCLUDE (' + 
                    STUFF((
                    SELECT ', [' + c.name + ']'
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
        FROM sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
            AND i.[type] = 2
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    ), '')

PRINT @SQL
--EXEC sys.sp_executesql @SQL

Çıktı:

CREATE TABLE [dbo].[WorkOut]
(
      [WorkOutID] BIGINT NOT NULL IDENTITY(1,1)
    , [TimeSheetDate] DATETIME NOT NULL
    , [DateOut] DATETIME NOT NULL
    , [EmployeeID] INT NOT NULL
    , [IsMainWorkPlace] BIT NOT NULL DEFAULT((1))
    , [DepartmentUID] UNIQUEIDENTIFIER NOT NULL
    , [WorkPlaceUID] UNIQUEIDENTIFIER NULL
    , [TeamUID] UNIQUEIDENTIFIER NULL
    , [WorkShiftCD] NVARCHAR(10) COLLATE Cyrillic_General_CI_AS NULL
    , [WorkHours] REAL NULL
    , [AbsenceCode] VARCHAR(25) COLLATE Cyrillic_General_CI_AS NULL
    , [PaymentType] CHAR(2) COLLATE Cyrillic_General_CI_AS NULL
    , [CategoryID] INT NULL
    , [Year] AS (datepart(year,[TimeSheetDate]))
    , CONSTRAINT [PK_WorkOut] PRIMARY KEY ([WorkOutID] ASC)
)

ALTER TABLE [dbo].[WorkOut] WITH CHECK ADD CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID])
ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]

CREATE NONCLUSTERED INDEX [IX_WorkOut_WorkShiftCD_AbsenceCode] ON [dbo].[WorkOut] ([WorkShiftCD] ASC, [AbsenceCode] ASC)
INCLUDE ([WorkOutID], [WorkHours])

Ayrıca bu makaleyi de kontrol edin -

Mevcut Tablo için CREATE TABLE Script Nasıl Oluşturulur: Bölüm 1


96
2018-05-28 16:53



Teşekkür ederim. Hesaplanan sütunlar ne olacak? Onları nasıl koruyabilirim? - enb081
Sorun değil :) Yarın hesaplanan sütunlar için destek ekliyorum. - Devart
Ayrıca, sütunların orjinal düzenlerini koruyarak alfabetik olarak yeniden sıralandığını fark ettim. - enb081
Lütfen güncellenmiş cevaba bakınız (hesaplanan sütunlar için destek ekledik, sütunlar için siparişi düzeltebilir ve sorguyu hızlandırabilir). - Devart
bu saf sanattı. - Daniel Sh.


Bir CREATE komut dosyası oluşturan bir TSQL komut dosyası oluşturmak istediğinizi mi yoksa Oluşturma komut dosyası oluşturmak için SQL SERVER Management Studio'daki Yönetim araçlarını mı kullanmak istiyorsunuz?

Eğer ikincisiyse, bir tabloyu sağ tıklatmanız ve Komut Dosyası Olarak Yap -> Oluştur -> Yeni Sorgu Penceresi seçmeniz yeterlidir.

Tüm veritabanının komut dosyasını istiyorsanız, veritabanına sağ tıklayın ve Görevler -> Komut Dosyaları Oluştur ... seçeneğini seçin ve ardından sihirbazı izleyin.

Aksi takdirde, çeşitli sistem tablolarından her türlü eğlenceli şeyleri seçmek meselesidir.


12
2018-04-01 18:04



Aslında, bunları bir gram komut dosyası oluşturmak için bir .NET uygulamasından çalıştırılacağı için bunları gramatik bir şekilde elde etmek için bir yol arıyordum. - cweston
Teşekkür ederim! Tek bir yerde tüm betiklerin betimlemesine sahip olmanın bir yolu olduğunu biliyordum, cevabın nerede olduğunu bulmak için bana işaret etti. - Tschallacka


Bu sorunun eski olduğunu anlıyorum, ama kısa bir süre önce koştum bir aramada patladı, bu yüzden yukarıdaki cevaba bir alternatif göndermeyi düşündüm.

Üretmek istiyorsanız create komutları programatik olarak .Net, içine bakmanızı şiddetle tavsiye ederim Sunucu Yönetimi Nesneleri (SMO) veya Dağıtılmış Yönetim Nesneleri (DMO) - kullandığınız SQL Server sürümüne bağlı olarak (eski 2005+, ikinci 2000). Bu kütüphaneleri kullanarak bir tabloyu betimlemek şu kadar kolay:

Server server      = new Server(".");
Database northwind = server.Databases["Northwind"];
Table categories   = northwind.Tables["Categories"];

StringCollection script = categories.Script();
string[] scriptArray    = new string[script.Count];

script.CopyTo(scriptArray, 0);

İşte Daha fazla bilgi içeren bir blog gönderisidir.


5
2018-05-09 13:27





Deneyin sp_helptext Tablolar için Eşdeğer mi?


3
2018-04-01 18:48





"En kolay yol, SQL Management Studio'nun yerleşik özelliğini kullanmaktır" fakat... Bir fonksiyon ve birkaç prosedürle çözdüm. Örneğin, 'table_name' adlı bir tablo için oluşturma tablosunu elde etmek için, sadece sp_ppinScriptTabla adı verilen prosedürü uygulamanız gerekir:

Exec sp_ppinScriptTabla 'table_name'

İşte tsql komut dosyası kodu:

Use Master
GO

Create Function sp_ppinTipoLongitud
(
    @xtype int,
    @length int,
    @isnullable int
)
Returns Varchar(512)
As 
Begin
    -- Función que a partir de un tipo de datos y una logitud, devuelve el texto del tipo.
    -- Por ejemplo: para xtype=varchar y length=10 devolverá "varchar(10)"
    Declare @ret varchar(512)
    Set @ret = ''

    Select @ret = t.name +
    Case When name in ('varchar', 'nvarchar', 'char', 'nchar') Then '(' + Convert(varchar, @length) + ')' Else '' End + ' ' +
    Case @isnullable When 1 Then 'NULL' Else 'NOT NULL' End
    From systypes t 
    Where t.xtype = @xtype

    Return @ret
End
GO

Create Procedure sp_ppinScriptLlavesForaneas
(
    @vchTabla sysname,
    @vchResultado varchar(8000) output
)
AS 
Begin

    DECLARE @tmpFK table(
        TablaF sysname,
        TablaR sysname,
        ColF sysname,
        ColR sysname,
        FKName sysname)

    -- obtengo las llaves foraneas en @vchForeign
    Declare @vchForeign varchar(8000), @FKName sysname, @vchColumnasF varchar(4000), @vchColumnasR varchar(4000), @ColF sysname, @ColR sysname
    Declare @vchTemp varchar(1000), @TablaR sysname

    Insert into @tmpFK
    Select TablaF.name AS TablaF, TablaR.name AS TablaR, ColF.name AS ColF, ColR.name AS ColR, ofk.name AS FKName
    From sysforeignkeys fk, sysobjects ofk, sysobjects TablaF, sysobjects TablaR, 
    syscolumns ColF, syscolumns ColR
    Where TablaF.name = @vchTabla
    And ofk.id = fk.constid
    And TablaF.id = fk.fkeyid
    And TablaR.id = fk.rkeyid
    And ColF.id = TablaF.id And ColF.colid = fk.fkey
    And ColR.id = TablaR.id And ColR.colid = fk.rkey
    order by FKName

    Set @vchForeign = ''
    While Exists ( Select * From @tmpFK )
    Begin
        Select Top 1 @FKName = FKName From @tmpFK
        Set @vchColumnasF = ''
        Set @vchColumnasR = ''
        While Exists ( Select * From @tmpFK Where FKName = @FKName )
        Begin
            Select Top 1 @ColF = ColF, @ColR = ColR, @TablaR = TablaR From @tmpFK Where FKName = @FKName
            Delete From @tmpFK Where ColF = @ColF And ColR = @ColR And TablaR = @TablaR And FKName = @FKName
            Set @vchColumnasF = @vchColumnasF + @ColF + ', '
            Set @vchColumnasR = @vchColumnasR + @ColR + ', '
        End

        Set @vchColumnasF = LEFT(@vchColumnasF, LEN(@vchColumnasF) - 1)
        Set @vchColumnasR = LEFT(@vchColumnasR, LEN(@vchColumnasR) - 1)
        Set @vchTemp = 'Constraint ' + @FKName + ' Foreign Key (' + @vchColumnasF + ') '
        Set @vchTemp = @vchTemp + 'References ' + @TablaR + ' (' + @vchColumnasR + ')'
        Set @vchForeign = @vchForeign + char(9) + @vchTemp + ',' + char(13) 
    End

    Select @vchResultado = Case When Len(@vchForeign) >=2 Then Left(@vchForeign, Len(@vchForeign) - 2) Else @vchForeign End
End
GO

Create Procedure sp_ppinScriptTabla
(
    @vchTabla sysname
)
AS

Set nocount on

-- Obtengo las foreign keys
Declare @foreign varchar(8000)
Exec sp_ppinScriptLlavesForaneas @vchTabla, @foreign output

-- SELECT que devuelve el script de Create Table de la tabla
Select 'Create ' + 
Case o.xtype When 'U' Then 'Table' When 'P' Then 'Procedure' Else '??' End + ' ' +
@vchTabla + char(13) + '('
From sysobjects o
Where o.name = @vchTabla
Union all
-- Campos + identitys + DEFAULTS
select char(9) + c.name + ' ' +                                 -- Nombre
dbo.sp_ppinTipoLongitud(t.xtype, c.length, c.isnullable) +          -- Tipo(longitud)
Case When c.colstat & 1 = 1                                     -- Identity (si aplica)
    Then ' Identity(' + convert(varchar, ident_seed(@vchTabla)) + ',' + Convert(varchar, ident_incr(@vchTabla)) + ')' 
    Else '' 
End + 
Case When not od.name is null                                   -- Defaults (si aplica)
    Then ' Constraint ' + od.name + ' Default ' + replace(replace(cd.text, '((', '('), '))', ')')
    Else ''
End + ', '
from sysobjects o, syscolumns c
LEFT OUTER JOIN sysobjects od On od.id = c.cdefault LEFT OUTER join syscomments cd On cd.id = od.id, 
systypes t
where o.id = object_id(@vchTabla)
and o.id = c.id
and c.xtype = t.xtype
Union all
-- Primary Keys y Unique keys
select char(9) + 'Constraint ' + o.name + ' ' +
Case o.xtype When 'PK' Then 'Primary Key' Else 'Unique' End + ' ' +
dbo.sp_ppinCamposIndice (db_name(), @vchTabla, i.indid) + ', '
from sysobjects o, sysindexes i
where o.parent_obj = object_id(@vchTabla)
and o.xtype in ('PK','UQ')
and i.id = o.parent_obj
and o.name = i.name
Union all
-- Check constraints
select char(9) + 'Constraint ' + o.name + ' Check ' + c.text + ', '
from sysobjects o, syscomments c
where o.parent_obj = object_id(@vchTabla)
and o.xtype in ('C')
and o.id = c.id
Union all
-- Foreign keys
Select @foreign
Union all
Select ')'

Set nocount off
GO

3
2018-01-19 22:06





Saklı yordamı veya işlev komut dosyasını eklemeyi unuttunuz. sp_ppinCamposIndice


2
2018-06-05 17:33



Burada bulun: github.com/thepirat000/All-Masters-SQL/blob/master/... - profimedica
Veya burada: pastebin.com/3APkqSar - profimedica


Bunu deneyin ("Sonuçları metne" kullanarak):

SELECT
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'V' OR sp.type = N'P' OR sp.type = N'RF' OR sp.type=N'PC')and(sp.name=N'YourObjectName' and SCHEMA_NAME(sp.schema_id)=N'dbo')
  • C: Kısıtlamayı kontrol et
  • D: Varsayılan kısıtlama
  • F: Yabancı Anahtar kısıtlaması
  • L: Giriş
  • P: Saklı yordam
  • PK: Birincil Anahtar kısıtlaması
  • RF: Çoğaltma Filtresi saklı yordamı
  • S: Sistem tablosu
  • TR: Tetikleyici
  • U: Kullanıcı tablosu
  • UQ: Benzersiz kısıtlama
  • V: Görüntüle
  • X: Genişletilmiş saklı yordam

Alkış,


2
2017-08-23 14:09