복붙노트

[SQL] SQL 쿼리 기존 테이블에 대한 스크립트를 생성 생성

SQL

SQL 쿼리 기존 테이블에 대한 스크립트를 생성 생성

나는 스크립트 데이터를 생성하지만이 나에게 반환하지 않습니다, 나는 어떻게 든 sys.tables를 조회하여이 작업을 수행 할 수 있습니다 가정합니다은 SQL 서버 2008에서 테이블을 기존의 스크립트를 만들려고 해요.

해결법

  1. ==============================

    1.가능한이 당신을 위해 도움이 될 수. 이 스크립트는 모든 테이블에 대한 인덱스, FK의, PK 및 일반 구조를 생성합니다.

    가능한이 당신을 위해 도움이 될 수. 이 스크립트는 모든 테이블에 대한 인덱스, FK의, PK 및 일반 구조를 생성합니다.

    예를 들어 -

    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]

    질문:

    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

    산출:

    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])

    또한이 문서를 확인 -

    어떻게 기존 테이블 TABLE 스크립트를 생성 생성하기 : 1 부

  2. ==============================

    2.당신은 당신이 스크립트를 작성하거나 작성 스크립트를 생성 SQL Server Management Studio의 관리 도구를 사용하여 생성하는 SQL 스크립트를 작성하고자하는 의미합니까?

    당신은 당신이 스크립트를 작성하거나 작성 스크립트를 생성 SQL Server Management Studio의 관리 도구를 사용하여 생성하는 SQL 스크립트를 작성하고자하는 의미합니까?

    이 후자의 경우는 단순히 테이블을 마우스 오른쪽 버튼으로 클릭하고, 스크립트 테이블로를 선택하는 문제이다 ->로 만들기 -> 새 쿼리 창.

    당신이 스크립트 전체 데이터베이스를 원하는 경우, 마우스 오른쪽 데이터베이스를 클릭하고 작업을 선택 -> 스크립트를 생성 ... 다음 마법사를 따라

    그렇지 않으면 다양한 시스템 테이블에서 재미있는 것들 모든 종류의 선택의 문제이다.

  3. ==============================

    3.나는이 질문이 오래 실현,하지만 난 위의 대답에 대한 대안을 게시 할 줄 알았는데 그래서 최근에, 난 그냥 도망 검색에서 팝업.

    나는이 질문이 오래 실현,하지만 난 위의 대답에 대한 대안을 게시 할 줄 알았는데 그래서 최근에, 난 그냥 도망 검색에서 팝업.

    당신이 닷넷 프로그래밍 스크립트를 만들 생성하기 위해 찾고 있다면, 내가보기 엔 서버 관리 개체 (SMO)로보고 추천 또는 분산 관리 (DMO) 개체 - 사용중인 SQL Server 버전에 따라 (전자는 2005 +입니다 ) 2000 후자. 이 라이브러리를 사용하여 테이블을 스크립팅하는 것은 쉬운 같은과 같다 :

    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);

    여기에 자세한 정보와 블로그 게시물입니다.

  4. ==============================

    4.테이블을위한하여 sp_helptext 등가를 시도?

    테이블을위한하여 sp_helptext 등가를 시도?

  5. ==============================

    5."가장 쉬운 방법은 사용하는 것입니다 내장 SQL 관리 Studio의 기능" 그러나... 나는 기능 및 절차의 몇 가지로 해결했다. 예를 들어, 당신이 sp_ppinScriptTabla라는 바로 프로 시저를 실행해야 'TABLE_NAME'라는 이름의 테이블에 대한 테이블을 생성 구하는 방법

    "가장 쉬운 방법은 사용하는 것입니다 내장 SQL 관리 Studio의 기능" 그러나... 나는 기능 및 절차의 몇 가지로 해결했다. 예를 들어, 당신이 sp_ppinScriptTabla라는 바로 프로 시저를 실행해야 'TABLE_NAME'라는 이름의 테이블에 대한 테이블을 생성 구하는 방법

    Exec sp_ppinScriptTabla 'table_name'

    여기 TSQL 스크립트 코드는 다음과 같습니다

    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
  6. ==============================

    6.당신은 sp_ppinCamposIndice의 저장 프로 시저 또는 함수 스크립트를 포함하는 것을 잊었다

    당신은 sp_ppinCamposIndice의 저장 프로 시저 또는 함수 스크립트를 포함하는 것을 잊었다

  7. ==============================

    7.당신이 임시 테이블에 대한 CREATE 스크립트를 얻을 수 있도록 여기에 @Devart의 대답에 약간의 변화입니다.

    당신이 임시 테이블에 대한 CREATE 스크립트를 얻을 수 있도록 여기에 @Devart의 대답에 약간의 변화입니다.

    @SQL 변수가 NVARCHAR (MAX) 데이터 형식이기 때문에 참고 그냥 단지 SSMS를 사용하여 결과에서 복사하지 못할 수 있습니다하시기 바랍니다. MAX는 필드의 가치를 얻는 방법을 보려면이 질문을 참조하십시오.

    DECLARE @temptable_objectid INT = OBJECT_ID('tempdb.db.#Temp');

DECLARE 
      @object_name SYSNAME
    , @object_id INT

SELECT  
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM tempdb.sys.objects o WITH (NOWAIT)
JOIN tempdb.sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE object_id = @temptable_objectid

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 tempdb.sys.index_columns ic WITH (NOWAIT)
    JOIN tempdb.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 tempdb.sys.foreign_key_columns k WITH (NOWAIT)
    JOIN tempdb.sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
    JOIN tempdb.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 tempdb.sys.columns c WITH (NOWAIT)
    JOIN tempdb.sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN tempdb.sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN tempdb.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 tempdb.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 tempdb.sys.index_columns ic WITH (NOWAIT)
                         JOIN tempdb.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 tempdb.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 tempdb.sys.foreign_keys fk WITH (NOWAIT)
        JOIN tempdb.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 tempdb.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)')
    ), '')

SELECT @SQL
  8. ==============================

    8.때문에 우리는 당신이 무엇을 요구 대안을 제공하고 있습니다 ..

    때문에 우리는 당신이 무엇을 요구 대안을 제공하고 있습니다 ..

    당신은 닷넷에 있다면, 당신은 Visual Studio에서 데이터베이스 게시 마법사 봐야한다. 스크립트 쉬운 방법 텍스트 파일로 테이블 / 데이터입니다.

    http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

  9. ==============================

    9.SSMS, 가장 쉬운 방법을 사용하여 당신은뿐만 아니라 그것을위한 옵션을 구성 할 수 있습니다 (예를 들어, 데이터 정렬, 구문, 드롭 ... 작성)

    SSMS, 가장 쉬운 방법을 사용하여 당신은뿐만 아니라 그것을위한 옵션을 구성 할 수 있습니다 (예를 들어, 데이터 정렬, 구문, 드롭 ... 작성)

    그렇지 않으면, 코드 플렉스에 SSMS 도구 팩 또는 DbFriend는 스크립트를 생성 할 수 있습니다

  10. ==============================

    10.이 질문에 내 대답을 참조하십시오 : SQL 서버에서 SQL 쿼리를 사용하여 테이블의 스크립트를 만들 생성하는 방법

    이 질문에 내 대답을 참조하십시오 : SQL 서버에서 SQL 쿼리를 사용하여 테이블의 스크립트를 만들 생성하는 방법

    이 쿼리를 사용합니다 :

    DROP FUNCTION [dbo].[Get_Table_Script]
Go

Create Function Get_Table_Script
(
    @vsTableName varchar(50)
)

Returns
    VarChar(Max)
With ENCRYPTION

Begin

Declare @ScriptCommand varchar(Max)

Select @ScriptCommand =
    ' Create Table [' + SO.name + '] (' + o.list + ')' 
    +
    (
        Case
        When TC.Constraint_Name IS NULL 
            Then ''
        Else 'ALTER TABLE ' + SO.Name + ' ADD CONSTRAINT ' +
            TC.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')'
        End
    )
From sysobjects As SO
    Cross Apply

    (
        Select 
            '  [' + column_name + '] ' + 
             data_type + 
             (
                Case data_type
                    When 'sql_variant' 
                        Then ''
                    When 'text' 
                        Then ''
                    When 'decimal' 
                        Then '(' + Cast( numeric_precision_radix 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 
            ) 
            + ' ' +
            (
                Case 
                    When Exists ( 
                                    Select id 
                                    From syscolumns
                                    Where 
                                        ( object_name(id) = SO.name )
                                        And 
                                        ( name = column_name )
                                        And 
                                        ( columnproperty(id,name,'IsIdentity') = 1 )
                                ) 
                        Then 'IDENTITY(' + 
                                Cast( ident_seed(SO.name) As varchar ) + ',' + 
                                Cast( ident_incr(SO.name) As varchar ) + ')'

                    Else ''

                End
            ) + ' ' +

            (
                Case 
                    When IS_NULLABLE = 'No' 
                        Then 'NOT ' 
                    Else '' 
                End 
            ) + 'NULL ' + 
            (
                Case 
                    When information_schema.columns.COLUMN_DEFAULT IS NOT NULL 
                        Then 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT 
                    ELse '' 
                End 
            ) + ', ' 
        From information_schema.columns 
        Where 
            ( table_name = SO.name )
        Order by ordinal_position
        FOR XML PATH('')) o (list)

        Inner Join information_schema.table_constraints As TC On (
                                                                    ( TC.Table_name = SO.Name )
                                                                    AND 
                                                                    ( TC.Constraint_Type  = 'PRIMARY KEY' )
                                                                    And 
                                                                    ( TC.TABLE_NAME = @vsTableName )
                                                                 )
        Cross Apply
            (
                Select '[' + Column_Name + '], '
                From  information_schema.key_column_usage As kcu
                Where 
                    ( kcu.Constraint_Name = TC.Constraint_Name )
                Order By ORDINAL_POSITION
                FOR XML PATH('')
            ) As j (list)
Where
    ( xtype = 'U' )
    AND 
    ( Name NOT IN ('dtproperties') )

Return @ScriptCommand

End

    그리고이 같은이 기능을 발사 할 수 있습니다 :

    Select [dbo].Get_Table_Script '<Your_Table_Name>'
  11. ==============================

    11.이 ( "텍스트 결과"를 사용)를보십시오 :

    이 ( "텍스트 결과"를 사용)를보십시오 :

    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')

    건배,

  12. ==============================

    12.우선 나는 devart에 의해 작성된 스크립트를 사랑하고 나는 그것을 사용하고 싶었,하지만 난 그것을 개선하기로 결정 그래서, 몇 가지 한계를 발견 :

    우선 나는 devart에 의해 작성된 스크립트를 사랑하고 나는 그것을 사용하고 싶었,하지만 난 그것을 개선하기로 결정 그래서, 몇 가지 한계를 발견 :

    내가 제대로 테스트 할 시간이 없었다 나는 단지 SQL 서버 4분의 2,012에 그것을 테스트

    최종 인쇄는 여전히 4000 개 문자로 제한되어 있음을 고려하지만, 변수는 전체 스크립트가 포함되어 있습니다.

    모든 의견 부탁드립니다.

    이 DevArt의 코드의 내 버전입니다 :

    DECLARE @object_id                          int;
DECLARE @SQL                                NVARCHAR(MAX) = N''
DECLARE @GenerateFKs                        bit = 1;
DECLARE @UseSourceCollation                 bit = 1;
DECLARE @GenerateIdentity                   bit = 1;
DECLARE @GenerateIndexes                    bit = 1;
DECLARE @GenerateConstraints                bit = 1;
DECLARE @GenerateKeyConstraints             bit = 1;
DECLARE @AssignConstraintNameOfDefaults     bit = 1;
DECLARE @AddDropIfItExists                  bit = 1;

------------------------ PLEASE SET the table name here -----------------
SET @object_id = object_ID(N'[dbo].[MyFancyTable]',N'U');
-------------------------------------------------------------------------

;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 and @GenerateFKs = 1
)
SELECT @SQL = 
    --------------------  DROP IS Exists --------------------------------------------------------------------------------------------------
        CASE WHEN @AddDropIfItExists = 1
        THEN 
            --Drop table if exists
            CAST(
                N'IF OBJECT_ID(''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''') IS NOT NULL DROP TABLE ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N';' + NCHAR(13) 
            as nvarchar(max))
            +
            --Drop foreign keys
            ISNULL(((
                SELECT 
                    CAST(
                        N'ALTER TABLE ' + quotename(s.name) + N'.' + quotename(t.name) + N' DROP CONSTRAINT ' + RTRIM(f.name) + N';' + NCHAR(13)
                    as nvarchar(max))
                FROM sys.tables t
                INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
                INNER JOIN sys.schemas      s ON s.schema_id = f.schema_id
                WHERE f.referenced_object_id = @object_id
                FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'))
            ,N'') + NCHAR(13)
        ELSE N'' END 
    +
    --------------------- CREATE TABLE -----------------------------------------------------------------------------------------------------------------
    CAST(
            N'CREATE TABLE ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + NCHAR(13) + N'(' + NCHAR(13) + STUFF((
            SELECT 
                CAST(
                    NCHAR(9) + N',' + quotename(c.name) + N' ' + 
                    CASE WHEN c.is_computed = 1
                        THEN N' AS ' + cc.[definition] 
                        ELSE UPPER(tp.name) + 
                            CASE WHEN tp.name IN (N'varchar', N'char', N'varbinary', N'binary', N'text')
                                    THEN N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length AS NVARCHAR(5)) END + N')'
                                    WHEN tp.name IN (N'nvarchar', N'nchar', N'ntext')
                                    THEN N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length / 2 AS NVARCHAR(5)) END + N')'
                                    WHEN tp.name IN (N'datetime2', N'time2', N'datetimeoffset') 
                                    THEN N'(' + CAST(c.scale AS NVARCHAR(5)) + N')'
                                    WHEN tp.name = N'decimal' 
                                    THEN N'(' + CAST(c.[precision] AS NVARCHAR(5)) + N',' + CAST(c.scale AS NVARCHAR(5)) + N')'
                                ELSE N''
                            END +
                            CASE WHEN c.collation_name IS NOT NULL and @UseSourceCollation = 1 THEN N' COLLATE ' + c.collation_name ELSE N'' END +
                            CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END +
                            CASE WHEN dc.[definition] IS NOT NULL THEN CASE WHEN @AssignConstraintNameOfDefaults = 1 THEN N' CONSTRAINT ' + quotename(dc.name) ELSE N'' END + N' DEFAULT' + dc.[definition] ELSE N'' END + 
                            CASE WHEN ic.is_identity = 1 and @GenerateIdentity = 1 THEN N' IDENTITY(' + CAST(ISNULL(ic.seed_value, N'0') AS NCHAR(1)) + N',' + CAST(ISNULL(ic.increment_value, N'1') AS NCHAR(1)) + N')' ELSE N'' END 
                    END + NCHAR(13)
                AS nvarchar(Max))
            FROM sys.columns c WITH (NOWAIT)
                INNER 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(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, NCHAR(9) + N' ')
    as nvarchar(max))
    + 
    ---------------------- Key Constraints ----------------------------------------------------------------
    CAST(
        case when @GenerateKeyConstraints <> 1 THEN N'' ELSE 
            ISNULL((SELECT NCHAR(9) + N', CONSTRAINT ' + quotename(k.name) + N' PRIMARY KEY ' + ISNULL(kidx.type_desc, N'') + N'(' + 
                        (SELECT STUFF((
                             SELECT N', ' + quotename(c.name) + N' ' + CASE WHEN ic.is_descending_key = 1 THEN N'DESC' ELSE N'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(N'.', N'NVARCHAR(MAX)'), 1, 2, N''))
                + N')' + NCHAR(13)
                FROM sys.key_constraints k WITH (NOWAIT) LEFT JOIN sys.indexes kidx ON
                    k.parent_object_id = kidx.object_id and k.unique_index_id = kidx.index_id
                WHERE k.parent_object_id = @object_id 
                    AND k.[type] = N'PK'), N'') + N')'  + NCHAR(13)
        END 
    as nvarchar(max))
    + 
    --------------------- FOREIGN KEYS -----------------------------------------------------------------------------------------------------------------
    CAST(
        ISNULL((SELECT (
            SELECT NCHAR(13) +
             N'ALTER TABLE ' + + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + + N' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN N' NOCHECK' 
                ELSE N' CHECK' 
              END + 
              N' ADD CONSTRAINT ' + quotename(fk.name)  + N' FOREIGN KEY(' 
              + STUFF((
                SELECT N', ' + quotename(k.cname) + N''
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'')
               + N')' +
              N' REFERENCES ' + quotename(SCHEMA_NAME(ro.[schema_id])) + N'.' + quotename(ro.name) + N' ('
              + STUFF((
                SELECT N', ' + quotename(k.rcname) + N''
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'')
               + N')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN N' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN N' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN N' ON DELETE SET DEFAULT' 
                ELSE N'' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN N' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN N' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN N' ON UPDATE SET DEFAULT'  
                ELSE N'' 
              END 
            + NCHAR(13) + N'ALTER TABLE ' + + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + + N' CHECK CONSTRAINT ' + quotename(fk.name)  + N'' + NCHAR(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(N'.', N'NVARCHAR(MAX)')), N'')
    as nvarchar(max))
    + 
    --------------------- INDEXES ----------------------------------------------------------------------------------------------------------
    CAST(
        ISNULL(((SELECT
             NCHAR(13) + N'CREATE' + CASE WHEN i.is_unique = 1 THEN N' UNIQUE ' ELSE N' ' END 
                    + i.type_desc + N' INDEX ' + quotename(i.name) + N' ON ' + + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + + N' (' +
                    STUFF((
                    SELECT N', ' + quotename(c.name) + N'' + CASE WHEN c.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END
                    FROM index_column c
                    WHERE c.is_included_column = 0
                        AND c.index_id = i.index_id
                    FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'') + N')'  
                    + ISNULL(NCHAR(13) + N'INCLUDE (' + 
                        STUFF((
                        SELECT N', ' + quotename(c.name) + N''
                        FROM index_column c
                        WHERE c.is_included_column = 1
                            AND c.index_id = i.index_id
                        FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'') + N')', N'')  + NCHAR(13)
            FROM sys.indexes i WITH (NOWAIT)
            WHERE i.[object_id] = @object_id
                AND i.is_primary_key = 0
                AND i.[type] in (1,2)
                and @GenerateIndexes = 1
            FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)')
        ), N'')
    as nvarchar(max))

PRINT @SQL
SELECT datalength(@SQL), @sql
--EXEC sys.sp_executesql @SQL
  13. ==============================

    13.가장 쉬운 방법은 SQL 관리 Studio의 내장 기능을 사용하는 것입니다.

    가장 쉬운 방법은 SQL 관리 Studio의 내장 기능을 사용하는 것입니다.

    , 작업로 이동, 데이터베이스를 마우스 오른쪽 단추로 클릭 스크립트를 생성하고, 마법사를 통해 도보. 당신은 스크립트 객체를 선택할 수 있습니다, 그것은 모두 당신을 위해 그것을 만들 수 있습니다.

    당신은 같은 일을 자신의 스크립트를 만들려고 노력하는 경우 이제, 당신은 아마 많은 작업을위한 최대있어 ...

    14. from https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] X SELECT * FROM WHERE 단정 한 ORM과 아이디 IN (...)  (0) 2020.03.31
[SQL] SQL 스위치 / 케이스에 '어디서'절  (0) 2020.03.31
[SQL] SQLite는 데이터베이스에서 중복 행을 삭제  (0) 2020.03.31
[SQL] DB를 모든 테이블, 행과 열에서 문자열 검색  (0) 2020.03.31
[SQL] 테이블에 MySQL의 :: 삽입, 다른 테이블에서 데이터?  (0) 2020.03.31
,

티스토리툴바