Hot answers tagged generate-script
6
You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.
It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.
This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess ...
5
As well as using SSMS, you can use SSMS Tools Pack
I prefer it: more options, batching, cleverer all round.
Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and ...
5
In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script.
However in practice this is nearly impossible to do. Wrapping a script in a ...
5
I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005. Red Gate definitely does not have such a restriction. It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.
4
Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object ...
4
I'm personally using only Visual Studio with Database Edition GDR, because it's better for my needs. What I like about it mostly is the fact that I'm able to compare db projects with the actual databases. The tool can also compare databases, not only projects.
It's true that the schema comparison tool itself is not very fast and has some quirks, but I ...
3
I don't know of any setting which will do this in SSMS. Microsoft probably does it so that they don't need to check the data type name for special characters when generating the script (user defined data type names could have anything in them) so they just box everything.
You could submit that as a feature request to the tools team via ...
3
I would not remove the GO statements that break up large batches. There are various reasons for breaking large inserts
size (duration) of transactions
memory requirements
transaction log management
mirror synchronicity
error isolation (by batch)
etc...
As for removing the status updates, that can be done easily. Press Ctrl-H or from the menu, Edit ...
2
There's nothing built-in from the command line.
If you have Red Gate SQL Compare you can do it:
sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q
SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO.
This is adapted from code found on this Simple Talk post.
...
2
To just feed off of squillman's answer this is to show a sample of what SQLPS can do for you...You can browse each "directory" under the database and just do a get-member -MemberType Method, looking for Script(). Most of the directories have it I believe.
Add-PSSnapin *SQL*
# Note my hostname of the server is "SQLSERVER"
# To show object names to be ...
1
You can use the SQL Server Database Publishing Wizard.
If you don't have it installed then you can grab version 1.2 from here
The installer doesn't give any indication that it has installed but if you open up a command window and navigate to:
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2 the SqlPubWiz.exe should be there.
You can ...
1
It seems [] is one of internal features. Just do macros in any truth editor to replace [INT], [VARCHAR]... to []less version or just replace all [ and ] with empty char.
ADDED:
select '[' + UPPER(name) + ']' from sys.types
Only top voted, non community-wiki answers of a minimum length are eligible
