Generate SQL Create Scripts for existing tables with Query
Possible this be helpful for you. This script generate indexes, FK's, PK and common structure for any table.
For example -
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]
Query:
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
Output:
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])
Also check this article -
How to Generate a CREATE TABLE Script For an Existing Table: Part 1
do you mean you wish to create a TSQL script which generates a CREATE script, or use the Management tools in SQL SERVER Management Studio to generate a Create script?
If it's the latter, it's a simply matter of right-clicking a table, and selecting Script Table As -> Create To -> New Query Window.
If you want the whole database scripted, then right click the database and select Tasks--> Generate Scripts... and then follow the wizard
otherwise it's a matter of selecting all sorts of fun things out of the various system tables.
I realize this question is old, but it recently popped up in a search I just ran, so I thought I'd post an alternative to the above answer.
If you are looking to generate create scripts programmatically in .Net, I would highly recommend looking into Server Management Objects (SMO) or Distributed Management Objects (DMO) -- depending on which version of SQL Server you are using (the former is 2005+, the latter 2000). Using these libraries, scripting a table is as easy as:
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);
Here is a blog post with more information.
First of all I love the script written by devart and I wanted to use it, but I found some limitation, so I decided to improve it:
- I fixed the bug that limits the script at 4000 chars (it's still possible that some crazy table still exceeds the limits)
- I fixed the bug/limitation in case the table uses a nonclustered primary key
- I replaced '[' with quotename
- I added the name of the default constraints
- I changed the logic to identify the source table
- I added the possibility to drop and recreate the table and its FKs
- I added the possibility to generate specific attributes
- I added the support for table compression
- I added the possibility the generate the scripts for any number of tables
- I fixed the limitation of 4000 varchar() when printing the result
- I replaced '' with N''
- I added the option to generate messages during the execution, because my final script (with the insert into) can take so long that I want to know what it's doing
- I added the generation of an "Insert into"
I didn't have time to test it properly and I tested it only on SQL Server 2012/4
The next version will change the generation of FKs because they need to be added at the end. Otherwise they may fail.
Any comment will be appreciated.
set transaction isolation level read uncommitted;
SET NOCOUNT ON;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
-- http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits
--NB: Crazy table can still have truncation at 4000 because of unexpected number of indexes or other very long list of columns/defaults etc
-- triggers are not supported
-- xml indexes are not supported
DECLARE @Tables table(id int identity(1,1), [name] sysname);
insert into @Tables([name])
values
('<yourSchema>.<youTableName>')
,('<yourSchema2>.<youTableName2>')
;
DECLARE @object_id int;
DECLARE @SourceDatabase nvarchar(max) = N'SourceTest'; --this is used only by the insert
DECLARE @TargetDatabase nvarchar(max) = N'DescTest'; --this is used only by the insert and USE <DBName>
--- options ---
DECLARE @UseTransaction bit = 0;
DECLARE @GenerateUseDatabase bit = 0;
DECLARE @GenerateFKs bit = 1;
DECLARE @GenerateIdentity bit = 1;
DECLARE @GenerateCollation bit = 0;
DECLARE @GenerateCreateTable bit = 1;
DECLARE @GenerateIndexes bit = 1;
DECLARE @GenerateConstraints bit = 1;
DECLARE @GenerateKeyConstraints bit = 1;
DECLARE @GenerateConstraintNameOfDefaults bit = 1;
DECLARE @GenerateDropIfItExists bit = 1;
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 = 2; --0 ignore set,1=set on, 2=set off
DECLARE @GenerateMessages bit = 1; --print with no wait
DECLARE @GenerateDataCompressionOptions bit = 1; --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
--SELECT * from sys.dm_db_database_page_allocations(DB_ID(), @object_ID, 0, 1, 'DETAILED')
-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--- Let's play
DECLARE @DataTypeSpacer int = 45; --this is just to improve the formatting of the script ...
DECLARE @name sysname;
DECLARE @SQL NVARCHAR(MAX) = N''
DECLARE db_cursor CURSOR FOR SELECT [name] from @Tables
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @object_id = object_ID(@name)
goto CreateScript;
backFromCreateScript:
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
return;
CreateScript:
DECLARE @CR NVARCHAR(max) = NCHAR(13);
DECLARE @TB NVARCHAR(max) = NCHAR(9);
DECLARE @CurrentIndent 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 and @GenerateFKs = 1
)
SELECT @SQL =
-------------------- USE DATABASE --------------------------------------------------------------------------------------------------
CAST(
CASE WHEN @GenerateUseDatabase = 1
THEN N'USE ' + @TargetDatabase + N';' + @CR
ELSE N'' END
as nvarchar(max))
+
-------------------- 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(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''',''SN'') IS NOT NULL DROP SYNONYM ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N';' + @CR
as nvarchar(max))
ELSE
CAST(
N''
as nvarchar(max))
END
+
-------------------- DROP IS Exists --------------------------------------------------------------------------------------------------
CASE WHEN @GenerateDropIfItExists = 1
THEN
--Drop table if exists
CAST(
N'IF OBJECT_ID(''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''',''U'') IS NOT NULL DROP TABLE ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N';' + @CR
as nvarchar(max))
+ @CR
ELSE N'' END
+
-------------------- DROP IS Exists --------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 and @GenerateDropFKIfItExists = 1 THEN
N'RAISERROR(''DROP CONSTRAINTS OF %s'',10,1, ''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_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 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'') + @CR
ELSE N'' END
+
--------------------- CREATE TABLE -----------------------------------------------------------------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 THEN
N'RAISERROR(''CREATE TABLE %s'',10,1, ''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''') WITH NOWAIT;' + @CR
ELSE N'' END) as nvarchar(max))
+
CASE WHEN @GenerateCreateTable = 1 THEN
CAST(
N'CREATE TABLE ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + @CR + N'(' + @CR + STUFF((
SELECT
CAST(
@TB + N',' + quotename(c.name) + N' ' + ISNULL(replicate(' ',@DataTypeSpacer - len(quotename(c.name))),'') --isnull(replicate) then len(quotename(c.name)) > @DataTypeSpacer
+
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 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, @TB + N' ')
as nvarchar(max))
ELSE
CAST('' as nvarchar(max))
end
+
---------------------- Key Constraints ----------------------------------------------------------------
CAST(
case when @GenerateKeyConstraints <> 1 THEN N'' ELSE
ISNULL((SELECT @TB + 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')' + @CR
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')' + @CR
END
as nvarchar(max))
+
CAST(
CASE
WHEN
@GenerateDataCompressionOptions = 1
AND
(SELECT top 1 data_compression_desc from sys.partitions where object_ID = @object_id and index_id = 1) <> N'NONE'
THEN
N'WITH (DATA_COMPRESSION=' + (SELECT top 1 data_compression_desc from sys.partitions where object_ID = @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(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''') WITH NOWAIT;' + @CR
ELSE N'' END) as nvarchar(max))
+
CAST(
ISNULL((SELECT (
SELECT @CR +
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
+ @CR + N'ALTER TABLE ' + + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + + N' CHECK CONSTRAINT ' + quotename(fk.name) + N'' + @CR
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((CASE WHEN @GenerateMessages = 1 and @GenerateIndexes = 1 THEN
N'RAISERROR(''CREATING INDEXES OF %s'',10,1, ''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_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(@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(@CR + 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'') + @CR
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))
ELSE
CAST(N'' as nvarchar(max))
END
+
------------------------ @GenerateDelete ----------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 and @GenerateDelete = 1 THEN
N'RAISERROR(''TRUNCATING %s'',10,1, ''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''') WITH NOWAIT;' + @CR
ELSE N'' END) as nvarchar(max))
+
CASE WHEN @GenerateDelete = 1 THEN
CAST(
(CASE WHEN exists (SELECT * FROM sys.foreign_keys WHERE referenced_object_id = @object_id) THEN
N'DELETE FROM ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N';' + @CR
ELSE
N'TRUNCATE TABLE ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N';' + @CR
END)
AS NVARCHAR(max))
ELSE
CAST(N'' as nvarchar(max))
END
+
------------------------- @GenerateInsertInto ----------------------------------------------------------
CAST((CASE WHEN @GenerateMessages = 1 and @GenerateDropFKIfItExists = 1 THEN
N'RAISERROR(''INSERTING INTO %s'',10,1, ''' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N''') WITH NOWAIT;' + @CR
ELSE N'' END) as nvarchar(max))
+
CASE WHEN @GenerateInsertInto = 1
THEN
CAST(
CASE WHEN EXISTS (SELECT * from sys.columns c where c.[object_id] = @object_id and is_identity = 1) AND @GenerateIdentityInsert = 1 THEN
N'SET IDENTITY_INSERT ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N' ON;' + @CR
ELSE
CAST('' AS nvarchar(max))
END
+
N'INSERT INTO ' + QUOTENAME(@TargetDatabase) + N'.' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N'('
+ @CR
+
(
@TB + N' ' + SUBSTRING(
(
SELECT @TB + ','+ quotename(Name) + @CR
from sys.columns c
where
c.[object_id] = @object_id
AND system_type_ID <> 189 /*timestamp*/
AND is_computed = 0
and (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
+
(
@TB + N' ' + SUBSTRING(
(
SELECT @TB + ','+ quotename(Name) + @CR
FROM sys.columns c
WHERE c.[object_id] = @object_id
and system_type_ID <> 189 /*timestamp*/
and is_computed = 0
and (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(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id))
+ N';' + @CR
+ CASE WHEN EXISTS (SELECT * from sys.columns c where c.[object_id] = @object_id and is_identity = 1) AND @GenerateIdentityInsert = 1 THEN
N'SET IDENTITY_INSERT ' + quotename(OBJECT_schema_name(@object_id)) + N'.' + quotename(OBJECT_NAME(@object_id)) + N' OFF;'+ @CR
ELSE
CAST('' AS nvarchar(max))
END
as nvarchar(max))
ELSE
CAST(
N''
as nvarchar(max))
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(max))
--print is limited to 4000 chars, if necessary, I use multiple print
--to maintain the consistency of the script, I split near the closest CrLF to the max chunk size
DECLARE @i int = 1;
DECLARE @maxChunk integer = 3990;
DECLARE @len integer = @maxChunk;
WHILE @i < len(@SQL)
BEGIN
IF len(@SQL) > (@i + @len)
set @len = len(substring(@SQL, @i, @maxChunk)) - CHARINDEX(@CR, reverse(substring(@SQL, @i, @len))) + 1
PRINT substring(@SQL, @i, @len)
set @i = @i + @len
set @len = @maxChunk
END
--SELECT datalength(@SQL), @sql
--EXEC sys.sp_executesql @SQL
goto backFromCreateScript;
Try sp_helptext Equivalent for Tables?