Создание сценариев SQL Create для существующих таблиц с помощью Query
Мне нужно иметь возможность получить сценарии CREATE для существующих таблиц в SQL Server 2008. Я предполагаю, что могу сделать это, запросив как-то sys.tables, однако это не возвращает мне данные сценария CREATE.
18 ответов
Решение
Возможно, это будет полезно для вас. Этот скрипт генерирует индексы, 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
Вы имеете в виду, что хотите создать сценарий TSQL, который генерирует сценарий CREATE, или использовать инструменты управления в SQL SERVER Management Studio для создания сценария Create?
Если это последнее, это просто вопрос щелчка правой кнопкой мыши по таблице и выбора Script Table As -> Create To -> New Query Window.
Если вы хотите, чтобы вся база данных была сценарием, щелкните правой кнопкой мыши базу данных и выберите Задачи -> Создать сценарии..., а затем следуйте указаниям мастера.
в противном случае это вопрос выбора всевозможных забавных вещей из различных системных таблиц.
Я понимаю, что этот вопрос старый, но он недавно всплыл в поиске, который я только что провел, поэтому я решил опубликовать альтернативу вышеуказанному ответу.
Если вы хотите создать create программные сценарии в.Net, я настоятельно рекомендую изучить объекты управления сервером (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);
Вот сообщение в блоге с дополнительной информацией.
Прежде всего, мне нравится сценарий, написанный devart, и я хотел его использовать, но я нашел некоторый предел, поэтому я решил улучшить его:
- Я исправил ошибку, которая ограничивает сценарий 4000 символами (все еще возможно, что некоторые сумасшедшие таблицы все еще превышают пределы)
- Я исправил ошибку / ограничение в случае, если таблица использует некластеризованный первичный ключ
- Я заменил '[' на имя
- Я добавил название ограничений по умолчанию
- Я изменил логику, чтобы идентифицировать исходную таблицу
- Я добавил возможность удалить и воссоздать таблицу и ее FKs
- Я добавил возможность генерировать определенные атрибуты
- Я заменил '' на 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
"Самый простой способ - использовать встроенную функцию 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
https://www.sqlservercentral.com/forums/topic/script-create-table-script-from-linked-server
Для LinkedServers - на SQLServer - отлично работает следующее -
ВЫБРАТЬ *
В MyNewTable
ИЗ MyLinkedServer.DatabaseName.dbo.TargetTable
ГДЕ 1 = 0
- данные не передаются, но таблица строится.
Вот небольшой вариант ответа @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
Вы забыли включить хранимую процедуру или скрипт функции для sp_ppinCamposIndice
Попробуйте это (используя "Результаты в текст"):
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: расширенная хранимая процедура
Ура,
Самый простой способ - использовать встроенную функцию SQL Management Studio.
Щелкните правой кнопкой мыши базу данных, перейдите к задачам, Генерация сценариев и пройдите мастер. Вы можете выбрать объекты для сценария, и он все сделает за вас.
Теперь, если вы пытаетесь заставить свой собственный скрипт сделать то же самое, вы, вероятно, готовы к большой работе...
мы можем позволить SQL сгенерировать скрипт таблицы, перейдя как
Таблица сценариев как> СОЗДАТЬ в> Новое окно редактора запросов
Одинокий тайник, первый плакат ...
Расширение решений @Devart и @ildanny ...
Мне нужно было запустить это для ВСЕХ таблиц в базе данных, а также выполнить код на связанных серверах.
Все таблицы ...
/*
Ex.
EXEC etl.GetTableDefinitions '{friendlyname}', '{DatabaseName}', 'All', 0, NULL;
EXEC etl.GetTableDefinitions '{friendlyname}', '{DatabaseName}', 'dbo', 0, NULL;
EXEC etl.GetTableDefinitions '{friendlyname}', '{DatabaseName}', 'All', 1, '{linkedservername}';
*/
CREATE PROCEDURE etl.GetTableDefinitions
(
@SystemName NVARCHAR(128)
, @DatabaseName NVARCHAR(128)
, @SchemaName NVARCHAR(128)
, @linkedserver BIT
, @linkedservername NVARCHAR(128)
)
AS
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @sql1 NVARCHAR(MAX) = N'';
DECLARE @inSchemaName NVARCHAR(MAX) = N'';
SELECT @inSchemaName = CASE WHEN @SchemaName = N'All' THEN N's.[name]' ELSE '''' + @SchemaName + '''' END;
IF @linkedserver = 0
BEGIN
SELECT @sql = N'
SET NOCOUNT ON;
--- options ---
DECLARE @UseTransaction BIT = 0;
DECLARE @GenerateUseDatabase BIT = 0;
DECLARE @GenerateFKs BIT = 0;
DECLARE @GenerateIdentity BIT = 1;
DECLARE @GenerateCollation BIT = 0;
DECLARE @GenerateCreateTable BIT = 1;
DECLARE @GenerateIndexes BIT = 0;
DECLARE @GenerateConstraints BIT = 1;
DECLARE @GenerateKeyConstraints BIT = 1;
DECLARE @GenerateConstraintNameOfDefaults BIT = 1;
DECLARE @GenerateDropIfItExists BIT = 0;
DECLARE @GenerateDropFKIfItExists BIT = 0;
DECLARE @GenerateDelete BIT = 0;
DECLARE @GenerateInsertInto BIT = 0;
DECLARE @GenerateIdentityInsert INT = 0; --0 ignore set,but add column; 1 generate; 2 ignore set AND column
DECLARE @GenerateSetNoCount INT = 0; --0 ignore set,1=set on, 2=set off
DECLARE @GenerateMessages BIT = 0; --print with no wait
DECLARE @GenerateDataCompressionOptions BIT = 0; --TODO: generates the compression option only of the TABLE, not the indexes
--NB: the compression options reflects the design VALUE.
--The actual compression of a the page is saved here
--- variables ---
DECLARE @DataTypeSpacer INT = 1; --this is just to improve the formatting of the script ...
DECLARE @name SYSNAME;
DECLARE @sql NVARCHAR(MAX) = N'''';
DECLARE @int INT = 1;
DECLARE @maxint INT;
DECLARE @SourceDatabase NVARCHAR(MAX) = N''' + @DatabaseName + '''; --this is used by the INSERT
DECLARE @TargetDatabase NVARCHAR(MAX) = N''' + @DatabaseName + '''; --this is used by the INSERT AND USE <DBName>
DECLARE @cr NVARCHAR(20) = NCHAR(13);
DECLARE @tab NVARCHAR(20) = NCHAR(9);
DECLARE @Tables TABLE
(
id INT IDENTITY(1,1)
, [name] SYSNAME
, [object_id] INT
, [database_id] SMALLINT
);
BEGIN
INSERT INTO @Tables([name], [object_id], [database_id])
SELECT s.[name] + N''.'' + t.[name] AS [name]
, t.[object_id]
, DB_ID(''' + @DatabaseName + ''') AS [database_id]
FROM [' + @DatabaseName + '].sys.tables t
JOIN [' + @DatabaseName + '].sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE t.[name] NOT IN (''Tally'',''LOC_AND_SEG_CAP1'',''LOC_AND_SEG_CAP2'',''LOC_AND_SEG_CAP3'',''LOC_AND_SEG_CAP4'',''TableNames'')
AND s.[name] = ' + @inSchemaName + '
ORDER BY s.[name], t.[name];
SELECT @maxint = COUNT(0)
FROM @Tables;
WHILE @int <= @maxint
BEGIN
;WITH
index_column AS
(
SELECT ic.[object_id]
, OBJECT_NAME(ic.[object_id], DB_ID(N''' + @DatabaseName + ''')) AS ObjectName
, ic.index_id
, ic.is_descending_key
, ic.is_included_column
, c.[name]
FROM [' + @DatabaseName + '].sys.index_columns ic WITH (NOLOCK)
JOIN [' + @DatabaseName + '].sys.columns c WITH (NOLOCK) ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
JOIN [' + @DatabaseName + '].sys.tables t ON c.[object_id] = t.[object_id]
)
, fk_columns AS
(
SELECT k.constraint_object_id
, cname = c.[name]
, rcname = rc.[name]
FROM [' + @DatabaseName + '].sys.foreign_key_columns k WITH (NOWAIT)
JOIN [' + @DatabaseName + '].sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id
AND rc.column_id = k.referenced_column_id
JOIN [' + @DatabaseName + '].sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id
AND c.column_id = k.parent_column_id
JOIN [' + @DatabaseName + '].sys.tables t ON c.[object_id] = t.[object_id]
WHERE @GenerateFKs = 1
)
SELECT @sql = @sql +
-------------------- USE DATABASE --------------------------------------------------------------------------------------------------
CAST(
CASE WHEN @GenerateUseDatabase = 1
THEN N''USE '' + @TargetDatabase + N'';'' + @cr
ELSE N'''' END
AS NVARCHAR(200))
+
-------------------- SET NOCOUNT --------------------------------------------------------------------------------------------------
CAST(
CASE @GenerateSetNoCount
WHEN 1 THEN N''SET NOCOUNT ON;'' + @cr
WHEN 2 THEN N''SET NOCOUNT OFF;'' + @cr
ELSE N'''' END
AS NVARCHAR(MAX))
+
-------------------- USE TRANSACTION --------------------------------------------------------------------------------------------------
CAST(
CASE WHEN @UseTransaction = 1
THEN
N''SET XACT_ABORT ON'' + @cr
+ N''BEGIN TRY'' + @cr
+ N''BEGIN TRAN'' + @cr
ELSE N'''' END
AS NVARCHAR(MAX))
+
-------------------- DROP SYNONYM --------------------------------------------------------------------------------------------------
CASE WHEN @GenerateDropIfItExists = 1
THEN CAST(N''IF OBJECT_ID('''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''',''''SN'''') IS NOT NULL DROP SYNONYM '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'';'' + @cr AS NVARCHAR(MAX))
ELSE CAST(N'''' AS NVARCHAR(MAX)) END
+
-------------------- DROP TABLE IF EXISTS --------------------------------------------------------------------------------------------------
CASE WHEN @GenerateDropIfItExists = 1
THEN
--Drop TABLE if EXISTS
CAST(N''IF OBJECT_ID('''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''',''''U'''') IS NOT NULL DROP TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'';'' + @cr AS NVARCHAR(MAX))
+ @cr
ELSE N'''' END
+
-------------------- DROP CONSTRAINT IF EXISTS --------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 AND @GenerateDropFKIfItExists = 1 THEN
N''RAISERROR(''''DROP CONSTRAINTS OF %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE N'''' END) AS NVARCHAR(MAX))
+
CASE WHEN @GenerateDropFKIfItExists = 1
THEN
--Drop foreign keys
ISNULL(((
SELECT
CAST(
N''ALTER TABLE '' + QUOTENAME(s.[name]) + N''.'' + QUOTENAME(t.[name]) + N'' DROP CONSTRAINT '' + RTRIM(f.[name]) + N'';'' + @cr
AS NVARCHAR(MAX))
FROM [' + @DatabaseName + '].sys.tables t
INNER JOIN [' + @DatabaseName + '].sys.foreign_keys f ON f.parent_object_id = t.[object_id]
INNER JOIN [' + @DatabaseName + '].sys.schemas s ON s.[schema_id] = f.[schema_id]
WHERE f.referenced_object_id = t.[object_id]
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)''))
, N'''') + @cr
ELSE N'''' END
+
--------------------- CREATE TABLE -----------------------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 THEN
N''RAISERROR(''''CREATE TABLE %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE CAST(N'''' AS NVARCHAR(MAX)) END) AS NVARCHAR(MAX))
+
CASE WHEN @GenerateCreateTable = 1 THEN
CAST(
N''CREATE TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + @cr + N''('' + @cr + STUFF((
SELECT
CAST(
@tab + N'','' + QUOTENAME(c.[name]) + N'' '' + ISNULL(REPLICATE('' '',@DataTypeSpacer - LEN(QUOTENAME(c.[name]))),'''')
+
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 @GenerateCollation = 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 @GenerateConstraintNameOfDefaults = 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 + @cr
AS NVARCHAR(MAX))
FROM [' + @DatabaseName + '].sys.columns c WITH (NOWAIT)
INNER JOIN [' + @DatabaseName + '].sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN [' + @DatabaseName + '].sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id]
AND c.column_id = cc.column_id
LEFT JOIN [' + @DatabaseName + '].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 [' + @DatabaseName + '].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] = t.[object_id]
ORDER BY c.column_id
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)''), 1, 2, @tab + N'' '') AS NVARCHAR(MAX))
ELSE CAST(N'''' AS NVARCHAR(MAX)) END
+
---------------------- Key Constraints ----------------------------------------------------------------
CAST(
CASE WHEN @GenerateKeyConstraints <> 1 THEN N''''
ELSE
ISNULL((SELECT @tab + 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 [' + @DatabaseName + '].sys.index_columns ic WITH (NOWAIT)
JOIN [' + @DatabaseName + '].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'')'' + @cr
FROM [' + @DatabaseName + '].sys.key_constraints k WITH (NOWAIT)
LEFT JOIN [' + @DatabaseName + '].sys.indexes kidx ON k.parent_object_id = kidx.[object_id]
AND k.unique_index_id = kidx.index_id
WHERE k.parent_object_id = t.[object_id]
AND k.[type] = N''PK''), N'''') + N'')'' + @cr
END
AS NVARCHAR(MAX))
+
CAST(
CASE
WHEN @GenerateDataCompressionOptions = 1 AND (SELECT TOP 1 data_compression_desc FROM [' + @DatabaseName + '].sys.partitions WHERE OBJECT_ID = t.[object_id] AND index_id = 1) <> N''NONE''
THEN N''WITH (DATA_COMPRESSION='' + (SELECT TOP 1 data_compression_desc FROM [' + @DatabaseName + '].sys.partitions WHERE OBJECT_ID = t.[object_id] AND index_id = 1) + N'')'' + @cr
ELSE N'''' + @cr
END AS NVARCHAR(MAX))
+
--------------------- FOREIGN KEYS -----------------------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 AND @GenerateDropFKIfItExists = 1 THEN
N''RAISERROR(''''CREATING FK OF %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE N'''' END) AS NVARCHAR(MAX))
+
CAST(
ISNULL((SELECT (
SELECT @cr +
N''ALTER TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_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]
AND fk.[object_id] = t.[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]
AND fk.[object_id] = t.[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
+ @cr + N''ALTER TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'' CHECK CONSTRAINT '' + QUOTENAME(fk.[name]) + N'''' + @cr
FROM [' + @DatabaseName + '].sys.foreign_keys fk WITH (NOWAIT)
JOIN [' + @DatabaseName + '].sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = t.[object_id]
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)'')), N'''')
AS NVARCHAR(MAX))
+
--------------------- INDEXES ----------------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 AND @GenerateIndexes = 1 THEN
N''RAISERROR(''''CREATING INDEXES OF %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE N'''' END) AS NVARCHAR(MAX))
+
CASE WHEN @GenerateIndexes = 1 THEN
CAST(
ISNULL(((SELECT
@cr + 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(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_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.[object_id] = t.[object_id]
AND c.index_id = i.index_id
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)''), 1, 2, N'''') + N'')''
+ ISNULL(@cr + N''INCLUDE ('' +
STUFF((
SELECT N'', '' + QUOTENAME(c.[name]) + N''''
FROM index_column c
WHERE c.is_included_column = 1
AND c.[object_id] = t.[object_id]
AND c.index_id = i.index_id
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)''), 1, 2, N'''') + N'')'', N'''') + @cr
FROM [' + @DatabaseName + '].sys.indexes i WITH (NOWAIT)
WHERE i.[object_id] = t.[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))
ELSE N'''' END
+
------------------------ @GenerateDelete ----------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 AND @GenerateDelete = 1 THEN
N''RAISERROR(''''TRUNCATING %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE N'''' END) AS NVARCHAR(MAX))
+
CASE WHEN @GenerateDelete = 1 THEN
CAST(
(CASE WHEN EXISTS (SELECT TOP 1 [name] FROM [' + @DatabaseName + '].sys.foreign_keys WHERE referenced_object_id = t.[object_id]) THEN
N''DELETE FROM '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'';'' + @cr
ELSE
N''TRUNCATE TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'';'' + @cr
END)
AS NVARCHAR(MAX))
ELSE N'''' END
+
------------------------- @GenerateInsertInto ----------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 AND @GenerateDropFKIfItExists = 1 THEN
N''RAISERROR(''''INSERTING INTO %s'''',10,1, '''''' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'''''') WITH NOWAIT;'' + @cr
ELSE N'''' END) AS NVARCHAR(MAX))
+
CASE WHEN @GenerateInsertInto = 1
THEN
CAST(
CASE WHEN EXISTS (SELECT TOP 1 c.[name] FROM [' + @DatabaseName + '].sys.columns c WHERE c.[object_id] = t.[object_id] AND c.is_identity = 1) AND @GenerateIdentityInsert = 1 THEN
N''SET IDENTITY_INSERT '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'' ON;'' + @cr
ELSE N'''' END
+
N''INSERT INTO '' + QUOTENAME(@TargetDatabase) + N''.'' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N''(''
+ @cr
+
(
@tab + N'' '' + SUBSTRING(
(
SELECT @tab + '',''+ QUOTENAME(c.[name]) + @cr
FROM [' + @DatabaseName + '].sys.columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_ID <> 189 /*timestamp*/
AND c.is_computed = 0
AND (c.is_identity = 0 or @GenerateIdentityInsert in (0,1))
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)'')
,3,99999)
)
+ N'')'' + @cr + N''SELECT ''
+ @cr
+
(
@tab + N'' '' + SUBSTRING(
(
SELECT @tab + '',''+ QUOTENAME(c.[name]) + @cr
FROM [' + @DatabaseName + '].sys.columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_ID <> 189 /*timestamp*/
AND c.is_computed = 0
AND (c.is_identity = 0 or @GenerateIdentityInsert in (0,1))
FOR XML PATH(N''''), TYPE).value(N''.'', N''NVARCHAR(MAX)'')
,3,99999)
)
+ N''FROM '' + @SourceDatabase + N''.'' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id]))
+ N'';'' + @cr
+ CASE WHEN EXISTS (SELECT TOP 1 c.[name] FROM [' + @DatabaseName + '].sys.columns c WHERE c.[object_id] = t.[object_id] AND c.is_identity = 1) AND @GenerateIdentityInsert = 1 THEN
N''SET IDENTITY_INSERT '' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], t.[database_id])) + N''.'' + QUOTENAME(OBJECT_NAME(t.[object_id], t.[database_id])) + N'' OFF;''+ @cr
ELSE N'''' END
AS NVARCHAR(MAX))
ELSE N'''' END
+
-------------------- USE TRANSACTION --------------------------------------------------------------------------------------------------
CAST(
CASE WHEN @UseTransaction = 1
THEN
@cr + N''COMMIT TRAN; ''
+ @cr + N''END TRY''
+ @cr + N''BEGIN CATCH''
+ @cr + N'' IF XACT_STATE() IN (-1,1)''
+ @cr + N'' ROLLBACK TRAN;''
+ @cr + N''''
+ @cr + N'' SELECT ERROR_NUMBER() AS ErrorNumber ''
+ @cr + N'' ,ERROR_SEVERITY() AS ErrorSeverity ''
+ @cr + N'' ,ERROR_STATE() AS ErrorState ''
+ @cr + N'' ,ERROR_PROCEDURE() AS ErrorProcedure ''
+ @cr + N'' ,ERROR_LINE() AS ErrorLine ''
+ @cr + N'' ,ERROR_MESSAGE() AS ErrorMessage; ''
+ @cr + N''END CATCH''
ELSE N'''' END
AS NVARCHAR(700))
FROM @Tables t
WHERE ID = @int
ORDER BY [name];
SET @int = @int + 1;
END
EXEC [master].dbo.PrintMax @sql;
/* see below for PrintMax code*/
END'
EXEC (@sql);
END
ELSE
И бит связанного сервера ...
BEGIN
SELECT @sql = N'EXECUTE (''
SET NOCOUNT ON;
BEGIN
... Same code but be sure to double up on your single quotes
END
... code for the printmax proc (not mine, @Ben B) because it may not exist at destination server
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset TINYINT; /*tracks the amount of offset needed */
DECLARE @String NVARCHAR(MAX);
SET @String = REPLACE(REPLACE(@sql, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10))
WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String) BETWEEN 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(CHAR(10), @String) -1
SET @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
SET @offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
SET @String = SUBSTRING(@String, @CurrentEnd + @offset, LEN(@String))
END
END'') AT [' + @linkedservername + ']';
EXEC (@sql);
END
В следующем :
Если вы хотите добавить расширенное свойство таблицы, добавьте этот код в конец :
DECLARE @SQL2 NVARCHAR(MAX) = (
SELECT CHAR(10)+Codes FROM (
SELECT
CONCAT(
'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''', CAST(EP.value AS nvarchar),'''', --Value
', @level0type=N''SCHEMA'',@level0name=N''',S.name,'''', --Schema
', @level1type=N''TABLE'',@level1name=N''',O.name,'''', --Table
ISNULL(', @level2type=N''COLUMN'',@level2name=N'''+C.name+'''', '') --Column
, CHAR(10), 'GO') As Codes
FROM
sys.all_objects O WITH (NOWAIT)
JOIN
sys.schemas S WITH (NOWAIT) ON S.schema_id = O.schema_id
LEFT JOIN
sys.extended_properties EP WITH (NOWAIT) ON EP.major_id = O.object_id
LEFT JOIN
sys.columns AS C WITH (NOWAIT) ON EP.minor_id = C.column_id AND EP.major_id = C.object_id
WHERE
S.name+'.'+O.name = @table_name
) A
FOR XML PATH('')
)
--replace XML escape characters
SET @SQL2=REPLACE(@SQL2, '
', CHAR(13))
SET @SQL2=REPLACE(@SQL2, '"', '"')
SET @SQL2=REPLACE(@SQL2, ''', '''')
SET @SQL2=REPLACE(@SQL2, '&', '&')
SET @SQL2=REPLACE(@SQL2, '<', '<')
SET @SQL2=REPLACE(@SQL2, '>', '>')
PRINT @SQL2
и если вы хотите добавить триггеры таблицы, добавьте этот код в конец ответе Девартаответа Девартаответа Деварта :
DECLARE @SQL3 NVARCHAR(MAX) = (
SELECT CHAR(10)+Codes FROM (
SELECT OBJECT_DEFINITION(object_id) + CHAR(10) + 'GO' Codes
FROM
sys.objects O WITH (NOWAIT)
WHERE type = 'TR' AND O.parent_object_id = OBJECT_ID(@table_name)
) A
FOR XML PATH('')
);
--replace XML escape characters
SET @SQL3=REPLACE(@SQL3, '
', CHAR(13))
SET @SQL3=REPLACE(@SQL3, '"', '"')
SET @SQL3=REPLACE(@SQL3, ''', '''')
SET @SQL3=REPLACE(@SQL3, '&', '&')
SET @SQL3=REPLACE(@SQL3, '<', '<')
SET @SQL3=REPLACE(@SQL3, '>', '>')
DECLARE @i int = 1;
DECLARE @maxChunk integer = 3990;
DECLARE @len integer = @maxChunk;
WHILE @i < len(@SQL3)
BEGIN
IF len(@SQL3) > (@i + @len)
set @len = len(substring(@SQL3, @i, @maxChunk)) - CHARINDEX(CHAR(10), reverse(substring(@SQL3, @i, @len))) + 1
PRINT substring(@SQL3, @i, @len)
set @i = @i + @len
set @len = @maxChunk
END
Так как мы предлагаем альтернативы тому, что вы спросили..
Если вы находитесь в.Net, вы должны взглянуть на Мастер публикации баз данных в Visual Studio. Простой способ записать ваши таблицы / данные в текстовый файл.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
@Devart: Спасибо, ответ был идеальным, я добавляю кое-что, что нашел во время кодирования.
@ildanny : Спасибо, что сняли ограничение на количество слов.
мои выводы:
Добавлены ограничения по умолчанию и проверка
Изменена идентификация:varchar(max) вместо char(1): некоторые таблицы могут начинаться с больших чисел
Триггеры также могут быть добавлены, поскольку они являются частью таблицы, определение доступно непосредственно из
select object_id from sys.triggers'
.
.
.
CASE WHEN dc.[definition] IS NOT NULL THEN ' CONSTRAINT ' + quotename(dc.name) + ' DEFAULT' + dc.[definition] ELSE '' END +
CASE WHEN ck.[definition] IS NOT NULL THEN ' CONSTRAINT ' + quotename(ck.name) + ' CHECK' + ck.[definition] ELSE '' END +
CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS VARCHAR) + ',' + CAST(ISNULL(ic.increment_value, '1') AS VARCHAR) + ')' ELSE '' END
.
.
.
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.check_constraints ck WITH (NOWAIT) ON c.[object_id] = ck.parent_object_id AND c.column_id = ck.parent_column_id
.
.
.
Использовать SSMS, самый простой способ. Вы также можете настроить параметры для него (например, параметры сортировки, синтаксис, отбросить... создать)
В противном случае SSMS Tools Pack или DbFriend на CodePlex могут помочь вам сгенерировать сценарии.
Смотрите мой ответ в этом вопросе: Как создать сценарий создания таблицы с помощью SQL-запроса в SQL Server
Используйте этот запрос:
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>'