Script Database, save selection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Script Database, save selection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-13 : 07:40:52

Sasa writes "Hi!
I know that you guys talked a lot about scripting databases, but I can't find any quick solution to my problem.
I have a huge database in which I need only half of objects scripted to file for regeneration on the other server.
Those are mostly views and procedures.
I can do that by using that option Script Database, but it's really painfull to select each object each time I need to do that (and I need to do this often).
Is there a way to save this selection for the next time?
Or maybe some utility that can do this?

Thank you,
Sasa"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 08:14:32

See if this helps you
http://sqljunkies.com/WebLog/madhivanan/archive/2005/09/27/16914.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 08:51:45

Hi Sasa , Welcome to SQL Team!

Probably a horrible idea, but here goes nothing!

Script the whole database.

Execute it on the "new machine"

DROP all the objects you don't need - make a script to drop them, so you can easily repeat it next time.

You can easily create a "Drop" script (from which you delete the objects you want to KEEP!) with something like:


SELECT 'DROP '
	+ CASE xtype
		WHEN 'U' THEN 'TABLE'
		WHEN 'V' THEN 'VIEW'
		WHEN 'FN' THEN 'FUNCTION'
		WHEN 'IF' THEN 'FUNCTION'
		WHEN 'TF' THEN 'FUNCTION'
		WHEN 'TR' THEN 'TRIGGER'
		WHEN 'P' THEN 'PROCEDURE'
-- ... etc ...
		ELSE '???' + xtype + '???'
		END
	+ ' ' + [name]
	+ CHAR(13) + CHAR(10) + 'GO'
FROM dbo.sysobjects
WHERE	    xtype NOT IN ('D', 'F', 'PK', 'S')
--	AND xtype IN ('U', 'V', 'P')	-- Restrict object types
ORDER BY xtype, [name]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources