如何生成这个脚本

[nattystyle]

在企业管理器中右键一张表->所有任务->生成SQL脚本

此时生成的脚本，只是生成这个表的表结构的脚本。

如何生成包含表结构也包含现有数据的SQL脚本？

[SQL77]

不行,生成脚本,然后再导入数据吧,

[htl258_Tony]

用自带的导入导出工具就能将结构与数据一起导入了.

[百年树人]

--将表数据生成SQL脚本的存储过程 

CREATE PROCEDURE dbo.UspOutputData 
@tablename sysname 
AS 
declare @column varchar(1000) 
declare @columndata varchar(1000) 
declare @sql varchar(4000) 
declare @xtype tinyint 
declare @name sysname 
declare @objectId int 
declare @objectname sysname 
declare @ident int 

set nocount on 
set @objectId=object_id(@tablename) 

if @objectId is null -- 判断对象是否存在 
begin 
print 'The object not exists' 
return 
end 
set @objectname=rtrim(object_name(@objectId)) 

if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 
begin 
print 'object not in current database' 
return 
end 

if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table 
begin 
print 'The object is not table' 
return 
end 

select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 

if @ident is not null 
print 'SET IDENTITY_INSERT '+@TableName+' ON' 

declare syscolumns_cursor cursor 

for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid 

open syscolumns_cursor 
set @column='' 
set @columndata='' 
fetch next from syscolumns_cursor into @name,@xtype 

while @@fetch_status < >-1 
begin 
if @@fetch_status < >-2 
begin 
if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 

begin 
set @column=@column+case when len(@column)=0 then'' else ','end+@name 

set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','',' 
end 

+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char 
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar 
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime 
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime 
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 
else @name end 

end 

end 

fetch next from syscolumns_cursor into @name,@xtype 

end 

close syscolumns_cursor 
deallocate syscolumns_cursor 

set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename 

print '--'+@sql 
exec(@sql) 

if @ident is not null 
print 'SET IDENTITY_INSERT '+@TableName+' OFF' 

GO 

exec UspOutputData 你的表名

[--小F--]

要么用导入导出工具
要不用存储过程
****************************************************************************
 软件名称: May Flower Erp
 版权所有: (C) 2005-2006 May Flower ERP 开发组
 功能描述: 获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)
----------------------------------------------------------------------------
 参数列表: 
        1: @TableName 需要创建脚本的表的名称
****************************************************************************
|</PRE>*/
CREATE PROCEDURE [dbo].[sp_HelpTable](@TableName sysname)
AS
  SET NOCOUNT ON
  DECLARE @ObjectID int
  DECLARE @TableScript table(Iden int IDENTITY(1, 1), ScriptLine nvarchar(4000))
  SET @ObjectID = object_id(@TableName)
  IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0
  BEGIN
    RAISERROR('指定的对象不是表对象', 16, 1)
    RETURN
  END 
  --获取表的创建脚本
  --插入表头
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'CREATE TABLE [' + USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].[' + object_name(@ObjectID) + N']('

  --插入字段
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'  [' + a.Name + N'] [' + b.name + N']' + 
           CASE WHEN c.Object_id IS NOT NULL THEN N' IDENTITY(' + CONVERT(nvarchar, c.seed_value) + N', ' + CONVERT(nvarchar, c.increment_value) + N')' 
                ELSE '' END +
           CASE WHEN b.xusertype IN (167, 175, 231, 239) THEN N'('+CONVERT(nvarchar, a.prec)  + N')'
                WHEN b.xusertype in (106, 108)           THEN N'('+CONVERT(nvarchar, a.xprec) + N', ' + CONVERT(nvarchar, a.xscale) + N')'
                ELSE '' END +
           CASE a.isnullable WHEN 1 THEN N'' ELSE N' NOT' END + N' NULL' +
           CASE WHEN d.Name IS NOT NULL THEN N' DEFAULT ' + d.Definition ELSE N'' END +
           N',' 
      FROM sys.syscolumns a
        LEFT JOIN sys.systypes b ON a.xusertype = b.xusertype
        LEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColID
        LEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID
    WHERE a.[ID] = @ObjectID
    ORDER BY a.ColOrder

  --插入主键和索引
  DECLARE @IndexID int, @IndexScript nvarchar(4000)
  DECLARE IndexCursor CURSOR FOR 
    SELECT b.Index_ID, N'  CONSTRAINT [' + a.Name + N'] ' + 
           CASE a.Type WHEN 'PK' THEN N'PRIMARY KEY ' WHEN 'UQ' THEN N'UNIQUE ' END + 
           CASE b.Type WHEN 1    THEN N'CLUSTERED'   WHEN 2     THEN N'NONCLUSTERED ' END + N'('
      FROM sys.key_constraints a
        LEFT JOIN sys.indexes b ON b.Object_ID = a.Parent_Object_ID AND b.index_id = a.unique_index_id
    WHERE a.Parent_Object_ID = @ObjectID
  OPEN IndexCursor
  FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @IndexScript = @IndexScript + N'[' + INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'
      FROM sys.index_columns 
    WHERE Object_ID = @ObjectID
      AND Index_ID = 2
    
    SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) -1) + N'),'
    INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)
    FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  END
  Close IndexCursor
  DEALLOCATE IndexCursor

  insert into @TableScript(ScriptLine) VALUES(')')
  select * from @TableScript 



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/10/14/4669511.aspx

[黄_瓜]

[Quote=引用 3 楼 josy 的回复:]
SQL code--将表数据生成SQL脚本的存储过程CREATEPROCEDURE dbo.UspOutputData@tablename sysnameASdeclare@columnvarchar(1000)declare@columndatavarchar(1000)declare@sqlvarchar(4000)declare@xtypetinyintdeclare@name sysnam?-
[/Quote]
xuexi

[vipper23]

在数据库上右键》任务》生成脚本》下一步》选择数据库》选项里把编写数据的脚本选成TRUE》选择表》....

[ShenLiang2025]

用个工具好了,

Red Gate的SQL Packager 6 可以完成此功能。

以下是该工具生成的代码示例:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
CREATE USER [test] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[db_datareader]
GO
GRANT CONNECT TO [test]
GO
PRINT N'Creating role db_datareader'
GO
EXEC sp_addrolemember N'db_datareader', N'test'
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [db_datareader].[j]'
GO
CREATE TABLE [db_datareader].[j]
(
[jno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[jname] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,
[jcity] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,
[balance] [numeric] (7, 2) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__j__31EC6D26] on [db_datareader].[j]'
GO
ALTER TABLE [db_datareader].[j] ADD CONSTRAINT [PK__j__31EC6D26] PRIMARY KEY CLUSTERED ([jno])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [db_datareader].[spj]'
GO
CREATE TABLE [db_datareader].[spj]
(
[sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[pno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[jno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[price] [numeric] (7, 2) NULL,
[qty] [smallint] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__spj__38996AB5] on [db_datareader].[spj]'
GO
ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [PK__spj__38996AB5] PRIMARY KEY CLUSTERED ([sno], [pno], [jno])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [db_datareader].[p]'
GO
CREATE TABLE [db_datareader].[p]
(
[pno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[pname] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[color] [char] (8) COLLATE Chinese_PRC_CI_AS NULL,
[weight] [int] NULL,
[price] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__p__24927208] on [db_datareader].[p]'
GO
ALTER TABLE [db_datareader].[p] ADD CONSTRAINT [PK__p__24927208] PRIMARY KEY CLUSTERED ([pno])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [db_datareader].[s]'
GO
CREATE TABLE [db_datareader].[s]
(
[sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[sname] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[status] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[saddr] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,
[man] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[dt] [datetime] NULL,
[tel] [char] (16) COLLATE Chinese_PRC_CI_AS NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__s__2E1BDC42] on [db_datareader].[s]'
GO
ALTER TABLE [db_datareader].[s] ADD CONSTRAINT [PK__s__2E1BDC42] PRIMARY KEY CLUSTERED ([sno])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [db_datareader].[spj]'
GO
ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [CK__spj__qty__3C69FB99] CHECK (([qty]>=(0) AND [qty]<=(10000)))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [db_datareader].[spj]'
GO
ALTER TABLE [db_datareader].[spj] ADD
CONSTRAINT [FK__spj__jno__3B75D760] FOREIGN KEY ([jno]) REFERENCES [db_datareader].[j] ([jno]),
CONSTRAINT [FK__spj__pno__3A81B327] FOREIGN KEY ([pno]) REFERENCES [db_datareader].[p] ([pno]),
CONSTRAINT [FK__spj__sno__398D8EEE] FOREIGN KEY ([sno]) REFERENCES [db_datareader].[s] ([sno])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
GRANT CONNECT TO [test]
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Drop constraints from [db_datareader].[spj]
ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__jno__3B75D760]
ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__pno__3A81B327]
ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__sno__398D8EEE]

-- Add 15 rows to [db_datareader].[spj]
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p1  ', N'j1  ', 22.60, 80)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p1  ', N'j4  ', 22.60, 60)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j1  ', 22.80, 100)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j4  ', 22.80, 60)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j6  ', 22.80, 6)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p3  ', N'j5  ', 22.10, 100)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j1  ', 11.90, 30)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j4  ', 11.90, 60)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j6  ', 11.90, 6)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's4  ', N'p2  ', N'j4  ', 11.90, 60)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's4  ', N'p2  ', N'j6  ', 33.80, 8)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j1  ', 22.80, 20)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j4  ', 22.80, 60)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j6  ', 22.80, 8)
INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's8  ', N'p3  ', N'j1  ', 13.00, 20)

-- Add constraints to [db_datareader].[spj]
ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__jno__3B75D760] FOREIGN KEY ([jno]) REFERENCES [db_datareader].[j] ([jno])
ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__pno__3A81B327] FOREIGN KEY ([pno]) REFERENCES [db_datareader].[p] ([pno])
ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__sno__398D8EEE] FOREIGN KEY ([sno]) REFERENCES [db_datareader].[s] ([sno])

COMMIT TRANSACTION
GO

[hzxsljp]

真好

[yananguo_1985]

只能导出生成SQL的脚本，导数据还是DTS

[szadv]

一直在学习