如何在ms sql server 2005中生成包含所有记录的脚本？ [英] how to generate script with all records in ms sql server 2005?

问题描述

嗨...

如何生成包含所有记录数据的脚本意味着ms sql server express 2005中的整个数据库

提前感谢

Hi...
How can i generate script with all record data means entire database in ms sql server express 2005
thanks in advance

推荐答案

我不相信我完全理解了你的问题（或者如果我不相信这是一件好事）。但是这里有一些代码会查询数据库中的每个表 - 我已经指出了你需要为脚本放置文本的位置。警告 - 这可能需要很长时间才能运行。

I''m not convinced I''ve understood your question fully (or if I have I''m not convinced that it''s a good thing to do). But here is some code that will query every table in your database - I''ve indicated where you would need to put the text for your script. Warning - this could take a long time to run.

DECLARE @SQLString nvarchar (255), 
@ParmDefinition nvarchar (255)
DECLARE @tablename sysname, @Empty char (1)
DECLARE FindTables CURSOR READ_ONLY FOR 
	SELECT TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES WITH(NOLOCK)
	WHERE TABLE_TYPE = ''BASE TABLE'' 
	--AND TABLE_NAME NOT LIKE ''XXX%'' -- Code any exclusions here
	AND TABLE_SCHEMA = ''dbo''
	ORDER BY TABLE_NAME
-- Note that if you want to narrow this down to columns in tables look at
-- select COLUMN_NAME, TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
OPEN FindTables
FETCH NEXT FROM FindTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
	-- This is the line you would need to change into whatever your "script" needs to be 
	SET @SQLString = N''(SELECT * FROM ['' + @tablename + ''] WITH(NOLOCK))''
	SET @ParmDefinition = N''@tablename sysname''
	RAISERROR(@tablename, 0, 1) WITH NOWAIT
	EXECUTE sp_executesql
		@SQLString, 
		@ParmDefinition, 
		@tablename = @tablename
	FETCH NEXT FROM FindTables INTO @tablename
END
CLOSE FindTables
DEALLOCATE FindTables

我已经重新阅读了你的问题以及我认为你真正追求的是什么一种生成脚本的方法，该脚本将重新创建整个数据库模式而不是所有数据，在这种情况下，请查看本教程...

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all -the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects / [ ^ ]

I''ve re-read your question and what I think you were really after was a way of generating a script that would recreate your entire database schema rather than all of the data in which case have a look at this tutorial here...
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/[^]

请查看下面的链接，它脚本对象和数据。

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy -database-with-data-generate-t-sql-for-insertion-data-from-one-table-to-another-table / [ ^ ]

Please have look on the link below, it scripts object along with data.
http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/[^]

这篇关于如何在ms sql server 2005中生成包含所有记录的脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！