Click here to Skip to main content
14,838,162 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi...
How can i generate script with all record data means entire database in ms sql server express 2005
thanks in advance
Posted

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
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/[^]
[Edit - 2nd point confirmed by OP so removed original solution]
   
v3
Comments
chetankhatri 28-Feb-13 5:57am
   
Respected sir,
my question is that i read your blog but i have nt see ansi padding property which you describe in your blog, my question is that i want to generate script with all inserted data with all primary key and foreign key constraint whatever all database file in single .sql file i have to just fire that .sql file to other pc and i want to get whole database in other pc..
thank you
CHill60 28-Feb-13 6:22am
   
My apologies - I missed the fact that you are using the Express edition. However, just skip over that part and progress through the wizard - some of the options may look a little different but you can experiment
chetankhatri 1-Mar-13 3:03am
   
Yes sir,thanks for reply in my case problem is that i also download publish wizard 1.1 but when i m going to generate script at that time error occured is TITLE: Microsoft SQL Server
------------------------------

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Database+Publishing+Wizard&ProdVer=1.1.1.0&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
BUTTONS:

OK
------------------------------
CHill60 1-Mar-13 9:53am
   
Sounds like an install problem ... try reinstalling sql server and/or make sure you've got all of the service pack updates installed
   
Comments
CHill60 1-Mar-13 4:42am
   
Trouble is the OP is using 2005 - it's a lot easier in 2008!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900