October 20, 2008 at 7:06 am
Hi Guys, Can any one help me out to know
How to generate the scripts of all tables without the Generate scripts wizard of SQL 2008.
Thanks
Manish Kaushik
- Manish
October 20, 2008 at 7:12 am
Take a look on a toolset from http://www.apexsql.com
October 20, 2008 at 7:55 am
You could also try to the tools from Red Gate (www.redgate.com).
You could use a coding language and make a call to SMO (SQL Management Objects) to generate the scripts. Since this is 2008, you could use PowerShell and calls to SMO to generate scripts. There's a great article over at Simple-Talk[/url] that could get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2008 at 8:15 am
Here is a link to a Simple-Talk article that explains how to do this for free with pwoershell in SQL Server 2008: http://www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 8:16 am
Ack! I spent so long looking for that article that you beat me to it Grant! :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 8:36 am
OK, here's my attempt to actually add some value here: Here is a post (by crever) that explains a very simple way to do this with powershell, including an instructional video at his blog: http://www.sqlservercentral.com/Forums/FindPost547249.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 8:44 am
Here are some tutorials over at JumpstartTV that might help too:
This one exactly answers the question:
http://www.jumpstarttv.com/generating-sql-server-scripts-using-smo_425.aspx?searchid=3347
This one discusses capturing changes
http://www.jumpstarttv.com/capture-schema-changes-with-smo_412.aspx?searchid=3347
Here's the full list.
http://www.jumpstarttv.com/Search.aspx?terms=smo
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2008 at 12:21 am
Manish Kaushik (10/20/2008)
Hi Guys, Can any one help me out to knowHow to generate the scripts of all tables without the Generate scripts wizard of SQL 2008.
Thanks
Manish Kaushik
You can use the following:
Step 1
Create the folder "MSSQLScripts" on the SQL Server box's hard disk drive.
Eg:
MKDIR d:\MSSQLScripts
Step 2
Execute the source code below in query analyzer.
set quoted_identifier off
go
use master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__GenerateScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__GenerateScript]
GO
create proc sp__GenerateScript
@dbname varchar(256) = '',
@Applicationpath varchar(700) ='C:\program files\Microsoft SQL Server\mssql\upgrade\',
@destinationpath varchar(700) ='D:\MSSQLScripts\',
@switches varchar(200) = ' /X /Y /A /q /r /G /I '
as
--Created by:MAK
--Date: Aug 28, 2004
--Objective: Generate SQl Scripts for all or given database
set quoted_identifier off
set nocount on
set concat_null_yields_null off
declare @count int
declare @folderexist int
declare @maxcount int
declare @query varchar(1000)
declare @date varchar(10)
set @date = convert(varchar(10),getdate(),112)
set @count =1
Print 'Generate Script - Started'
print getdate()
set @Applicationpath = @Applicationpath +'scptxfr.exe'
create table #dbtable (id int identity(1,1), dbname varchar(256))
if @dbname = ''
begin
insert into #dbtable (dbname) select name from
master..sysdatabases
where name != 'tempdb' and status & 32 != 32
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824
end
else
begin
insert into #dbtable (dbname) select name from
master..sysdatabases
where name != 'tempdb' and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824
and name = @dbname
end
if (select count(*) from #dbtable) = 0
begin
set @date = convert(varchar(100), getdate(),109)
Print 'Error: No valid database found for Generating Script'
end
else
begin
set @destinationpath = @destinationpath +@date
create table #files (Files int, Folder int, parent int)
insert #files exec master.dbo.xp_fileexist @destinationpath
select @folderexist = Folder from #files
if @folderexist <>1
begin
set @query = 'MKDIR "'+@destinationpath+'"'
print @query
exec master..xp_cmdshell @query
set @destinationpath = @destinationpath
end
else
begin
print 'Information:'+ @destinationpath + ' already exist. Skipping Folder Creation'
end
set @maxcount = (select max(id) from #dbtable)
While @count <= @maxcount
begin
set @dbname = (select dbname from #dbtable where id = @count)
set @query = '"'+@applicationpath +'"'+ ' /s '+@@servername+ ' /d '+@dbname+ +' /F '+@destinationpath+ @switches
set @query = @query
print @query
exec master..xp_cmdshell @query
if @@error <> 0
begin
Print 'Error'
end
set @count = @count+1
end
end
print getdate()
Print 'Generate Script - Completed'
--Usage:
--exec sp__GenerateScript '','D:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript
--exec sp__GenerateScript 'MSDB','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript 'TOM','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--xp_cmdshell '"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL /d Anand /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I'
Parameter 1 is the database name. If you leave it blank, it scripts all the databases. Eg: '' or 'Master'. Default value is ''
Parameter 2 is the path information of scptxfr.exe Default value is 'C:\program files\Microsoft SQL Server\mssql\upgrade\'
Parameter 3 is the path information of the destination folder where you would like the scripts do be dumped. Default value is 'D:\MSSQLScripts\'
Parameter 4 is the various optional switches used in scptxfr.exe Default value is ' /X /Y /A /q /r /G /I '
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 21, 2008 at 12:40 am
That's really interesting, but I do not seem to have scptxfr.exe or the path 'C:\program files\Microsoft SQL Server\mssql\upgrade\'?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 12:53 am
Use Microsoft SQL Server Database Publishing Wizard, this tool may solve your problem.
Check the URL: http://www.microsoft.com/downloads/details.aspx?FamilyID=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
October 21, 2008 at 12:53 am
rbarryyoung (10/21/2008)
That's really interesting, but I do not seem to have scptxfr.exe or the path 'C:\program files\Microsoft SQL Server\mssql\upgrade\'?
scptxfr.exe is missing from the SQL-server 2005 versions. you can find this in the sql server 2000.
more info
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 5:08 am
Hi Ten Centuries
I Installed the Microsoft SQL Server Database Publishing Wizard from the given location but unfortunately its not for SQL 2008 as while connecting I got this following message
"This SQL Server version (10.0) is not supported. (Microsoft.SqlServer.ConnectionInfo)"
- Manish
October 22, 2008 at 5:34 am
Then, if you're not going to get one of the third party tools mentioned above, you should look at a solution using PowerShell or TSQL. Personally, for something like this, I'd lean towards PowerShell.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2008 at 8:34 am
Powershell is definitely the way to do this in SQL Server 2008.
For reasons that have always mystified me, the SQL scripter has never been available from within SQL itself.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 3:44 am
Hi SSCarpal Tunnel,
I tried the way as you mentioned the url
http://www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/
Thats very nice I am able to generate the script, but I am unable to get the primary keys and foreign keys on the script. When I execute the scripts, all the table created but failed to have the primary keys and foreign keys.
- Manish
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply