반응형
쿼리를 사용하여 기존 테이블에 대한 SQL 작성 스크립트 생성
SQL Server 2008 내의 기존 테이블에 대한 CREATE 스크립트를 가져 오려고합니다. sys.tables를 쿼리하여이 작업을 수행 할 수 있다고 가정하지만 CREATE 스크립트 데이터가 반환되지는 않습니다.
이것은 당신에게 도움이 될 수 있습니다. 이 스크립트는 모든 테이블에 대한 인덱스, 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])
이 기사도 확인하십시오-
기존 테이블에 대한 CREATE TABLE 스크립트를 생성하는 방법 : 1 부
CREATE 스크립트를 생성하는 TSQL 스크립트를 만들거나 SQL SERVER Management Studio의 관리 도구를 사용하여 Create 스크립트를 생성 하시겠습니까?
후자의 경우 테이블을 마우스 오른쪽 단추로 클릭하고 다음으로 스크립트 테이블-> 생성 대상-> 새 쿼리 창을 선택하기 만하면됩니다.
전체 데이터베이스를 스크립팅하려면 데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업-> 스크립트 생성 ...을 선택한 다음 마법사를 따릅니다.
그렇지 않으면 다양한 시스템 테이블에서 모든 종류의 재미있는 것들을 선택하는 문제입니다.
이 질문이 오래되었다는 것을 알고 있지만 최근에 방금 실행 한 검색에서 팝업되었으므로 위의 답변에 대한 대안을 게시 할 것이라고 생각했습니다.
create.Net에서 프로그래밍 방식으로 스크립트 를 생성 하려는 경우 사용중인 SQL Server 버전에 따라 SMO ( 서버 관리 개체 ) 또는 DMO ( 분산 관리 개체 )를 살펴 보는 것이 좋습니다 (전자는 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);
여기에 자세한 정보와 블로그 게시물입니다.
"가장 쉬운 방법은 SQL Management Studio의 기본 제공 기능을 사용하는 것입니다."하지만 ... 기능과 몇 가지 절차로 문제를 해결했습니다. 예를 들어 'table_name'이라는 테이블에 대한 테이블 생성을 얻으려면 sp_ppinScriptTabla라는 프로 시저 만 실행해야합니다.
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
저장 프로 시저 또는 함수 스크립트를 포함하는 것을 잊었습니다. sp_ppinCamposIndice
가장 쉬운 방법은 SQL Management Studio의 기본 제공 기능을 사용하는 것입니다.
데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업, 스크립트 생성으로 이동하여 마법사를 진행합니다. 스크립팅 할 개체를 선택할 수 있으며 모든 것이 만들어집니다.
이제 동일한 작업을 수행하기 위해 자신의 스크립트를 만들려고한다면 아마도 많은 작업을해야 할 것입니다.
다음을 시도해보십시오 ( "텍스트에 대한 결과"사용).
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 : 제약 조건 확인
- D : 기본 제약
- F : 외래 키 제약
- L : 로그
- P : 저장 프로 시저
- PK : 기본 키 제약
- RF : 복제 필터 저장 프로 시저
- S : 시스템 테이블
- TR : 트리거
- U : 사용자 테이블
- UQ : 고유 제약
- V :보기
- X : 확장 저장 프로 시저
건배,
@Devart의 답변에 대한 약간의 변형이 있으므로 임시 테이블에 대한 CREATE 스크립트를 얻을 수 있습니다.
@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
우리는 당신이 요청한 것에 대한 대안을 제공하고 있기 때문에 ..
.Net에있는 경우 Visual Studio의 데이터베이스 게시 마법사를 확인해야합니다. 테이블 / 데이터를 텍스트 파일로 스크립팅하는 쉬운 방법.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
SSMS 사용, 가장 쉬운 방법 옵션도 구성 할 수 있습니다 (예 : 데이터 정렬, 구문, 드롭 ... 생성).
그렇지 않으면 SSMS 도구 팩 또는 CodePlex의 DbFriend를 사용하여 스크립트를 생성 할 수 있습니다.
이 질문에서 내 대답을 참조하십시오 : SQL Server에서 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
그리고 다음 Function과 같이 발사 할 수 있습니다 .
Select [dbo].Get_Table_Script '<Your_Table_Name>'
우선 저는 devart가 작성한 스크립트를 좋아하고 그것을 사용하고 싶었지만 약간의 한계를 발견하여 개선하기로 결정했습니다.
- 스크립트를 4000 자로 제한하는 버그를 수정했습니다 (일부 미친 테이블이 여전히 제한을 초과 할 수 있음).
- 테이블이 클러스터되지 않은 기본 키를 사용하는 경우 버그 / 제한 사항을 수정했습니다.
- '['를 quotename으로 바꿨습니다.
- 기본 제약의 이름을 추가했습니다.
- 소스 테이블을 식별하기 위해 논리를 변경했습니다.
- 테이블과 해당 FK를 삭제하고 다시 만들 수있는 가능성을 추가했습니다.
- 특정 속성을 생성 할 수있는 가능성을 추가했습니다.
- ''를 N ''으로 바꿨습니다.
제대로 테스트 할 시간이 없었고 SQL Server 2012/4에서만 테스트했습니다.
최종 인쇄는 여전히 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
반응형
'programing tip' 카테고리의 다른 글
| Android 재정의 onBackPressed () (0) | 2020.12.28 |
|---|---|
| Apache에서 .svn 폴더에 대한 액세스 거부 (0) | 2020.12.27 |
| ASP.NET MVC 로그인 ReturnUrl이 항상 NULL입니까? (0) | 2020.12.27 |
| Maven에서 생성 된 jar에서 리소스를 제외하는 방법은 무엇입니까? (0) | 2020.12.27 |
| byte []로 드로어 블 (0) | 2020.12.27 |