将数据库模式导出到 SQL 文件中

您是否尝试过 SQLManagementStudio 中的 Generate Scripts(右击、任务、生成脚本)选项？这是否产生了您所说的“ SQL 文件”？

可以通过 SQLServerManagementStudio 为文件生成脚本，步骤如下:

1. 右键单击要为其生成脚本的数据库(而不是表) ，并选择 Task-generatedscript
2. 接下来，选择所请求的表/表、视图、存储过程等(从选择特定的数据库对象中)
3. 单击 Advanced-选择要编写脚本的数据类型
4. 单击“下一步”并完成

生成脚本

在生成脚本时，有一个区域允许您编写脚本、约束、键等。在 SQLServer2008R2脚本中有一个高级选项:

我编写这个 sp 来自动创建所有东西的模式，pk，fk，分区，约束..。 我写的是在同一个地方跑步。

重要! ！ 在执行之前

    create type TableType as table (ObjectID int)

这里的 SP:

create PROCEDURE [dbo].[util_ScriptTable]
@DBName SYSNAME
,@schema sysname
,@TableName SYSNAME
,@IncludeConstraints BIT = 1
,@IncludeIndexes BIT = 1
,@NewTableSchema sysname
,@NewTableName SYSNAME = NULL
,@UseSystemDataTypes BIT = 0
,@script varchar(max) output
AS
BEGIN try
if not exists (select * from sys.types where name = 'TableType')
create type TableType as table (ObjectID int)--drop type TableType
    

declare @sql nvarchar(max)
  

DECLARE @MainDefinition TABLE (FieldValue VARCHAR(200))
--DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
    

--SET @DBName = DB_NAME(DB_ID())
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
    

DECLARE @ShowFields TABLE (FieldID INT IDENTITY(1,1)
,DatabaseName VARCHAR(100)
,TableOwner VARCHAR(100)
,TableName VARCHAR(100)
,FieldName VARCHAR(100)
,ColumnPosition INT
,ColumnDefaultValue VARCHAR(100)
,ColumnDefaultName VARCHAR(100)
,IsNullable BIT
,DataType VARCHAR(100)
,MaxLength varchar(10)
,NumericPrecision INT
,NumericScale INT
,DomainName VARCHAR(100)
,FieldListingName VARCHAR(110)
,FieldDefinition CHAR(1)
,IdentityColumn BIT
,IdentitySeed INT
,IdentityIncrement INT
,IsCharColumn BIT
,IsComputed varchar(255))
    

DECLARE @HoldingArea TABLE(FldID SMALLINT IDENTITY(1,1)
,Flds VARCHAR(4000)
,FldValue CHAR(1) DEFAULT(0))
    

DECLARE @PKObjectID TABLE(ObjectID INT)
    

DECLARE @Uniques TABLE(ObjectID INT)
    

DECLARE @HoldingAreaValues TABLE(FldID SMALLINT IDENTITY(1,1)
,Flds VARCHAR(4000)
,FldValue CHAR(1) DEFAULT(0))
    

DECLARE @Definition TABLE(DefinitionID SMALLINT IDENTITY(1,1)
,FieldValue VARCHAR(200))


  

set @sql=
'
use '+@DBName+'
SELECT distinct DB_NAME()
,inf.TABLE_SCHEMA
,inf.TABLE_NAME
,''[''+inf.COLUMN_NAME+'']'' as COLUMN_NAME
,CAST(inf.ORDINAL_POSITION AS INT)
,inf.COLUMN_DEFAULT
,dobj.name AS ColumnDefaultName
,CASE WHEN inf.IS_NULLABLE = ''YES'' THEN 1 ELSE 0 END
,inf.DATA_TYPE
,case inf.CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(inf.CHARACTER_MAXIMUM_LENGTH AS varchar) end--CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
,CAST(inf.NUMERIC_PRECISION AS INT)
,CAST(inf.NUMERIC_SCALE AS INT)
,inf.DOMAIN_NAME
,inf.COLUMN_NAME + '',''
,'''' AS FieldDefinition
--caso di viste, dà come campo identity ma nn dà i valori, quindi lo ignoro
,CASE WHEN ic.object_id IS not NULL and ic.seed_value is not null THEN 1 ELSE 0 END AS IdentityColumn--CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
,CASE WHEN c.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
,cc.definition
from (select schema_id,object_id,name from sys.views union all select schema_id,object_id,name from sys.tables)t
--sys.tables t
join sys.schemas s on t.schema_id=s.schema_id
JOIN sys.columns c ON  t.object_id=c.object_id --AND s.schema_id=c.schema_id
LEFT JOIN sys.identity_columns ic ON t.object_id=ic.object_id AND c.column_id=ic.column_id
left JOIN sys.types st ON st.system_type_id=c.system_type_id and st.principal_id=t.object_id--COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = c.default_object_id AND dobj.type = ''D''
left join sys.computed_columns cc on t.object_id=cc.object_id and c.column_id=cc.column_id
join INFORMATION_SCHEMA.COLUMNS inf on t.name=inf.TABLE_NAME
and s.name=inf.TABLE_SCHEMA
and c.name=inf.COLUMN_NAME
WHERE inf.TABLE_NAME = @TableName and inf.TABLE_SCHEMA=@schema
ORDER BY inf.ORDINAL_POSITION
'
    

print @sql
INSERT INTO @ShowFields( DatabaseName
,TableOwner
,TableName
,FieldName
,ColumnPosition
,ColumnDefaultValue
,ColumnDefaultName
,IsNullable
,DataType
,MaxLength
,NumericPrecision
,NumericScale
,DomainName
,FieldListingName
,FieldDefinition
,IdentityColumn
,IdentitySeed
,IdentityIncrement
,IsCharColumn
,IsComputed)
                                    

exec sp_executesql @sql,
N'@TableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@schema=@schema
/*
SELECT @DBName--DB_NAME()
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,CAST(ORDINAL_POSITION AS INT)
,COLUMN_DEFAULT
,dobj.name AS ColumnDefaultName
,CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END
,DATA_TYPE
,CAST(CHARACTER_MAXIMUM_LENGTH AS INT)
,CAST(NUMERIC_PRECISION AS INT)
,CAST(NUMERIC_SCALE AS INT)
,DOMAIN_NAME
,COLUMN_NAME + ','
,'' AS FieldDefinition
,CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn
,CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed
,CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement
,CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'


WHERE c.TABLE_NAME = @TableName
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
*/
SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields
    

INSERT INTO @HoldingArea (Flds) VALUES('(')
    

INSERT INTO @Definition(FieldValue)VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @DBName + '.' + @NewTableSchema + '.' + @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
INSERT INTO @Definition(FieldValue)VALUES('(')
INSERT INTO @Definition(FieldValue)
SELECT   CHAR(10) + FieldName + ' ' +
--CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) +CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
        

CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName +
CASe WHEN IsNullable = 1 THEN ' NULL '
ELSE ' NOT NULL '
END
ELSE
case when IsComputed is null then
UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')'
ELSE
CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')'
ELSE
CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')'
ELSE ''
end
end
END +
CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN IsNullable = 1 THEN ' NULL '
ELSE ' NOT NULL '
END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + replace(ColumnDefaultName,@TableName,@NewTableName) + '] DEFAULT' + UPPER(ColumnDefaultValue)
ELSE ''
END
else
' as '+IsComputed+' '
end
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN ''
ELSE ','
END
        

FROM    @ShowFields
    

IF @IncludeConstraints = 1
BEGIN
        

set @sql=
'
use '+@DBName+'
SELECT  distinct  '',CONSTRAINT ['' + @NewTableName+''_''+replace(name,@TableName,'''') + ''] FOREIGN KEY ('' + ParentColumns + '') REFERENCES ['' + ReferencedObject + '']('' + ReferencedColumns + '')''
FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + '',''
FROM   sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + '',''
FROM   sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('''')   )), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
inner join sys.schemas s on fk.schema_id=s.schema_id and s.name=@schema) a
WHERE ParentObject = @TableName
'
        

print @sql
        

INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
/*
SELECT    ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM ( SELECT   ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name
,   REVERSE(SUBSTRING(REVERSE((   SELECT cp.name + ','
FROM   sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((   SELECT cr.name + ','
FROM   sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id   FOR XML PATH('')   )), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk    ) a
WHERE ParentObject = @TableName
*/
            

set @sql=
'
use '+@DBName+'
SELECT distinct '',CONSTRAINT ['' + @NewTableName+''_''+replace(c.name,@TableName,'''') + ''] CHECK '' + definition
FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and s.name=@schema
WHERE OBJECT_NAME(parent_object_id) = @TableName
'
            

print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
/*
SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
*/
            

set @sql=
'
use '+@DBName+'
SELECT DISTINCT  PKObject = cco.object_id
FROM    sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
'
print @sql
INSERT INTO @PKObjectID(ObjectID)
exec sp_executesql @sql,
N'@TableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@schema=@schema
/*
SELECT DISTINCT  PKObject = cco.object_id
FROM    sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE    OBJECT_NAME(parent_object_id) = @TableName    AND  i.type = 1 AND    is_primary_key = 1
*/
            

set @sql=
'
use '+@DBName+'
SELECT DISTINCT    PKObject = cco.object_id
FROM    sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
'
print @sql
INSERT INTO @Uniques(ObjectID)
exec sp_executesql @sql,
N'@TableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@schema=@schema
/*
SELECT DISTINCT    PKObject = cco.object_id
FROM    sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE    OBJECT_NAME(parent_object_id) = @TableName AND  i.type = 2 AND    is_primary_key = 0 AND    is_unique_constraint = 1
*/
            

SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
            

declare @t TableType
insert @t select * from @PKObjectID
declare @u TableType
insert @u select * from @Uniques
            

set @sql=
'
use '+@DBName+'
SELECT distinct '',CONSTRAINT '' + @NewTableName+''_''+replace(cco.name,@TableName,'''') + CASE type WHEN ''PK'' THEN '' PRIMARY KEY '' + CASE WHEN pk.ObjectID IS NULL THEN '' NONCLUSTERED '' ELSE '' CLUSTERED '' END  WHEN ''UQ'' THEN '' UNIQUE '' END + CASE WHEN u.ObjectID IS NOT NULL THEN '' NONCLUSTERED '' ELSE '''' END
+ ''(''+REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''
FROM   sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id
order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')''
FROM sys.key_constraints cco
inner join sys.schemas s on cco.schema_id=s.schema_id and s.name=@schema
LEFT JOIN @U u ON cco.object_id = u.objectID
LEFT JOIN @t pk ON cco.object_id = pk.ObjectID
WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName
            

'
            

print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TableType readonly,@u TableType readonly',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema,@t=@t,@u=@u
            

/*
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END  WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END
+ '(' +REVERSE(SUBSTRING(REVERSE(( SELECT   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM   sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE i.object_id = ccok.parent_object_id AND   ccok.object_id = cco.object_id    FOR XML PATH(''))), 2, 8000)) + ')'
FROM sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE    OBJECT_NAME(cco.parent_object_id) = @TableName
*/
END
           

INSERT INTO @Definition(FieldValue) VALUES(')')
        

set @sql=
'
use '+@DBName+'
select '' on '' + d.name + ''([''+c.name+''])''
from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2)
join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id)
join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id)
join sys.schemas s on t.schema_id=s.schema_id
join sys.data_spaces d on i.data_space_id=d.data_space_id
where t.name=@TableName and s.name=@schema
order by key_ordinal
'
        

print 'x'
print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@schema=@schema
             

IF @IncludeIndexes = 1
BEGIN
set @sql=
'
use '+@DBName+'
SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '+@DBName+'.'+@NewTableSchema+'.'+@NewTableName+' (''
+   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN '' DESC'' ELSE '' ASC'' END + '',''
FROM  sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id
and is_included_column=0
ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000)) + '')''+
ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE((   SELECT name + '',''
FROM  sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE  t.name=@TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id
and is_included_column=1
ORDER BY key_ordinal ASC   FOR XML PATH('''')    )), 2, 8000))+'')'' ,'''')+''''
FROM sys.indexes i join sys.tables t on i.object_id=t.object_id
join sys.schemas s on t.schema_id=s.schema_id
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0
where t.name=@TableName and s.name=@schema
'
            

print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema,@ClusteredPK=@ClusteredPK
            

END
       

/*
                

SELECT   'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +   REVERSE(SUBSTRING(REVERSE((   SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM  sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE  OBJECT_NAME(sc.object_id) = @TableName AND  sc.object_id = i.object_id AND  sc.index_id = i.index_id
ORDER BY index_column_id ASC   FOR XML PATH('')    )), 2, 8000)) + ')'
FROM sys.indexes i
WHERE   OBJECT_NAME(object_id) = @TableName
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1   AND is_unique_constraint = 0   AND is_primary_key = 0
               

*/
            

INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC
            

----------------------------------
--SELECT FieldValue+'' FROM @MainDefinition FOR XML PATH('')
set @script='use '+@DBName+' '+(SELECT FieldValue+'' FROM @MainDefinition FOR XML PATH(''))
            

--declare @q    varchar(max)
--set @q=(select replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')),'</FieldValue>',''))
--set @script=(select REPLACE(@q,'<FieldValue>',''))
--drop type TableType
END try
-- ##############################################################################################################################################################################
BEGIN CATCH
BEGIN
-- INIZIO  Procedura in errore =========================================================================================================================================================
PRINT '***********************************************************************************************************************************************************'
PRINT 'ErrorNumber               : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX))
PRINT 'ErrorSeverity             : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX))
PRINT 'ErrorState                : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX))
PRINT 'ErrorLine                 : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX))
PRINT 'ErrorMessage              : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX))
PRINT '***********************************************************************************************************************************************************'
-- FINE  Procedura in errore =========================================================================================================================================================
END
set @script=''
return -1
END CATCH
-- ##############################################################################################################################################################################

执行:

declare @s varchar(max)
exec [util_ScriptTable]   'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
select @s

In the picture you can see. In the set script options, choose the last option: Types of data to script you click at the right side and you choose what you want. 您应该选择这个选项来导出架构和数据