Generate SQL Create Scripts for existing tables with Query

Sql Problem Overview

I'm trying to get the CREATE scripts for existing tables within SQL Server 2008. I assume I can do this by querying the sys.tables somehow, however this isn't returning me the CREATE script data.

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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.

Solution 3 - Sql

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.

Solution 4 - Sql

"Easiest way is to use the built-in feature of SQL Management Studio" but... I have resolved it with a function and a couple of procedures. For example, to obtain the create table for a table named 'table_name', you have to execute just the procedure called sp_ppinScriptTabla:

Exec sp_ppinScriptTabla 'table_name'

Here is the tsql script code:

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

Solution 5 - Sql

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;

Solution 6 - Sql

Try sp_helptext Equivalent for Tables?

Solution 7 - Sql

You forgot to include the stored procedure or function script for sp_ppinCamposIndice

Solution 8 - Sql

Here's a slight variation on @Devart 's answer so you can get the CREATE script for a temp table.

Please note that since the @SQL variable is an NVARCHAR(MAX) data type you might not be able to copy it from the result using just only SSMS. Please see this question to see how to get the full value of a MAX field.

DECLARE @temptable_objectid INT = OBJECT_ID('tempdb..#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

Solution 9 - Sql

https://www.sqlservercentral.com/forums/topic/script-create-table-script-from-linked-server

For LinkedServers - on SQLServer - following works great -

SELECT *

INTO MyNewTable

FROM MyLinkedServer.DatabaseName.dbo.TargetTable

WHERE 1 = 0

--no data transferred, but the table gets built.

Solution 10 - Sql

we can let the SQL to generate create table script by navigating as

Script Table as > CREATE To > New Query Editor Window

Solution 11 - Sql

Easiest way is to use the built-in feature of SQL Management Studio.

Right-click the database, go to tasks, Generate Scripts, and walk through the wizard. You can choose what objects to script, and it'll make it all for you.

Now if you are trying to make your OWN script to do the same thing, you're probably up for a lot of work...

Solution 12 - Sql

Since we're offering alternatives to what you asked..

If you're in .Net, you should look at the Database Publishing Wizard in Visual Studio. Easy way to script your tables/data to a text file.

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

Solution 13 - Sql

Use the SSMS, easiest way You can configure options for it as well (eg collation, syntax, drop...create)

Otherwise, SSMS Tools Pack, or DbFriend on CodePlex can help you generate scripts

Solution 14 - Sql

See my answer in this Question : How to generate create script of table using SQL query in SQL Server

Use this query :

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

And you can fire this Function like this :

Select [dbo].Get_Table_Script '<Your_Table_Name>'

Solution 15 - Sql

Try this (using "Results to text"):

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: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure

Cheers,

Solution 16 - Sql

Lone time lurker, first time poster...

Expanding on @Devart and @ildanny solutions...

I had the need to run this for ALL tables in a db and also to execute the code against Linked Servers.

All tables...

/*
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

And the Linked Server bit...

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

Ben B solution

Content Type	Original Author	Original Content on Stackoverflow
Question	cweston	View Question on Stackoverflow
Solution 1 - Sql	Devart	View Answer on Stackoverflow
Solution 2 - Sql	Stephen Wrighton	View Answer on Stackoverflow
Solution 3 - Sql	Bobby D	View Answer on Stackoverflow
Solution 4 - Sql	Federico Colombo	View Answer on Stackoverflow
Solution 5 - Sql	ildanny	View Answer on Stackoverflow
Solution 6 - Sql	John MacIntyre	View Answer on Stackoverflow
Solution 7 - Sql	Aaron Thomason	View Answer on Stackoverflow
Solution 8 - Sql	Sal	View Answer on Stackoverflow
Solution 9 - Sql	RedBottleSanitizer	View Answer on Stackoverflow
Solution 10 - Sql	mabdullahse	View Answer on Stackoverflow
Solution 11 - Sql	BradC	View Answer on Stackoverflow
Solution 12 - Sql	madcolor	View Answer on Stackoverflow
Solution 13 - Sql	jerryhung	View Answer on Stackoverflow
Solution 14 - Sql	Ardalan Shahgholi	View Answer on Stackoverflow
Solution 15 - Sql	Sp4	View Answer on Stackoverflow
Solution 16 - Sql	Le Poissons	View Answer on Stackoverflow