Create a database of only selected objects with a script

Posted on 2010-09-03
Medium Priority
Last Modified: 2012-06-21
Hello I am wanting to create a database back from a pre existing database.  However I want to pick and choose objects I want and the data contained therein.  I don't want to mirror or backup the database.  I want to create a script to create the database and applicable objects only that I can pick and choose and make a script that can generate the data objects and some of their actual data on command.  

The datababase is a SQL Server Enterprise 2005 Database.  I can preuse the existing database to get my objects, it's not down, it just needs to be trimmed.  I would like to get meta data to move based on a config table to pick and choose what to move(I am not set on this and if you have a better idea I'm all eyes).  I want a script preferrably so I can alter it as data changes later for new schemas, tables, functions, etc.

Below is my code that I have started.  I would like to know how are some built in way to move objects and data with the least possible effort but also I can expand them with a where clause to pick and choose what to move.

Any help is much appreciated.
use TempLoc

if object_ID('FTG.TablesInMain') is not null
	drop table FTG.TablesInMain

create table FTG.TablesInMain
		DatabaseName	varchar(64)
	,	SchemaName	varchar(32)
	,	TableName		varchar(64)
	,	TableType		varchar(16)
	,	Remarks		varchar(64)  NULL
	,	Remove_Keep	bit

Use Main

insert into TempLoc.FTG.TablesInMain
	,	SchemaName
	,	TableName
	,	TableType
	,	Remarks
exec sp_tables

update FTG.TablesInMain
set Remove_Keep = 1  -- 1 is keep in my example

Where it get's interesting to actually set up data to be 

update FTG.TablesinMain
set Remove_Keep = 0
where TableName in (specifics will be added later to hunt for unneccary tables)

exec (some sproc to go off this temp table to move data based on bit reference in Keep_Remove table

need more help on possible functions that could actually move
the data and other built ins that find the triggers and such.  
If I could use the Object_ID for most of this I would be fine
with that I just need to know the syntax of finding the objects
and moving them

Open in new window

Question by:CNCDeveloper
  • 3
  • 2
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33598159
Why not just use the database scripting already provided in Management studio then transfer your data with DTS

in sql management studio
Right click on Database - Taks - Generate scripts

This will let you select what objects you want to script then save your script to a file.
You can the use the import export wizard to transfer your data

Author Comment

ID: 33600857
I did not know that part of the SSMS existed, thanks.  

I will go with this method but is there any way to make it run based object sets I set saying what to copy and what not to?  The database I am copying has close to 380 tables, 456 stored procedures, and numerous other things.  To go through a wizard may take a while.

For the data transfer I was hoping to keep it with a single script but pick and choose data to transfer.  You can fashion your query with DTS instead of just bulk copying a table correct?

I know the wizards can do a lot but it needs a person unchecking a lot of stuff too.
LVL 75

Accepted Solution

Anthony Perkins earned 750 total points
ID: 33601306
>>The database I am copying has close to 380 tables, 456 stored procedures, and numerous other things.  To go through a wizard may take a while.<<
The good news: You can do this with SSMS by first selecting all the tables and then outputing the script.
The bad news: You will have to do it for each object and worse then that you will have to do it once for each table in order to get the indexes.

What you really need is a third party tool to do this.  Red-Gate's SQL Compare comes to mind.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 33843584
Well unfortunately what I really needed was a dynamic scripting data tool.  I understand that during complex procedures it is not always easy to explain what you want.  The scripting tool in SSMS is very limited in that it may generate errors when copying massive tables (more than 3 million rows).  I wanted to create a tool to generate scripts.  Eventually what I did was below.  This code is an example of code but it is basically three parts with a fourth left up to the user to generate a list of tables from their

1st part is to get your column values from the sys.columns data table so you can create a 'select *' into a single string.  I chose to make a view to give me something like 'select cast(column1 as varchar(max)) + ', ' + cast(column2 as varchar(max)) ... etc.

2nd part is to dynamically select this data from the view as a procedure.  I select the string to run as a long string then run it dynamically in SQL.

3rd Part 2 is just for a single script so to really get fast on hundreds of tables I need to loop this.  I set up a looping sproc to run this from.

4th Part is the table that part 3 is going to run from.  You need a master list of tables and criteria column to base what tables you want to keep or not on.

There are certain issues this of course will run into:

1. Varchar(max) does not deal well with varbinary.  You could go a level deeper than I did and add case logic to your column listings in the view.  That required too much I thought as my database although massive, did not have but 6 tables that stored in varbinary, I just did those manually.

2. Constraints may upset this process.  I would find someone else online that has a dynamic way to remove all constraints and print them out for reapplying them once data insertion is done.

3. Not all tables store an identity column so I could have set a bit somewhere if the table had an identity column or not.  Did not do that.  It's pretty simple thought to just remove the two lines that reference that though.

4. You should not run this script on tables over 5 million rows except one at a time and you may get an ASP.Net error if you are trying to copy and paste out of SSMS.  I would recommend on the extremely large tables running part 2 by itself on the table listed out explicitly and to file.

This tool actually helps a lot though as the SSMS wizard is slow, clunky, you need to set options.  There may be things I don't have as stated though like RedGate's tools. Or there may be SSIS things to automate the existing wizard faster.  I did not see much though except recreating the wheel each time for a dataset I wanted to copy.  I could have done multiple ones but you would have had to point and click one at a time and save it.  I chose my method because mainly I could run it FAST.  

Sorry I did not reply earlier and I know Redgate probably has a publishing tool that puts this to shame.

-- Part 1 is to create a view that can take the columns apart.  I --- need to create a string to create a script from.
create view {MySchema}.vIdentityInsert as

with obj as
		s.name + '.' + o.name as TableName
	,	o.name as tbl
	,	o.object_id
	,	o.create_date
	,	o.modify_date
	from {MyDatabase}.sys.objects o
		join Creditnet.sys.schemas s on o.schema_id = s.schema_id
		and o.type_desc = 'USER_TABLE'
, cols as 
	,	obj.object_id
	,	obj.tbl
	,	create_date
	,	modify_date
	,	(
			name as n
		from {MyDatabase}.sys.columns c
		where c.object_id = obj.object_id
		for xml auto
		) as cols
	from obj
	where TableName in (select TableName from DataStaging.FTG.CreditnetTables)
, report as 
	,	object_id
	,	tbl
	--,	create_date
	--,	modify_date
	,	replace 
			left(right(cols,len(cols) -6),len(cols)-9)
					, '"/><c n="', '{|}'
					 as ColumnsData
	from cols
,	'Insert into ' + TableName + ' ( ' + replace(ColumnsData, '{|}',',') + ' ) Values' as insertstmt
,	'isnull(cast(' + replace(ColumnsData, '{|}', ' as varchar(max)),''NULL'') + ''{|}'' + isnull(cast(') + 
	' as varchar(max)), ''NULL'') as Data' as TableInsert
from report

-- part 2 is to create a procedure that can script this data but 
-- be dependent on the table you put in to get it's sys object ------ values from

if object_id('{MySchema}.spScripter') is not null
	drop proc {MySchema}.spScripter

create proc Brett.spScripter
		@Table	varchar(128)


	@Cols				varchar(max)
,	@Ins				varchar(256)
,	@SQL				varchar(max)

	@Cols = TableInsert
,	@Ins =	insertstmt
from {MySchema}.vIdentityInsert
where TableName = @Table

Set @SQL = 
declare @Temp table ( Data	varchar(max)  )

insert into @Temp values (''Use {NewDatabaseName}'')

insert into @Temp values (''set identity_insert ' + @Table + ' on'')

insert into @Temp
select ' + @Cols +

from Creditnet.' + @Table + '

update @Temp
set Data = '''''''' + replace(Data,''{|}'','''''','''''') + ''''''''
where Data != ''set identity_insert ' + @Table + ' on'' 
and Data != ''Use {NewDatabaseName}''

update @Temp
set Data = replace(Data,''''''NULL'''''',''NULL'')
where Data like ''%NULL%''

update @Temp
set Data = ''' + @Ins + ' ('' + Data + '')''
where Data != ''set identity_insert ' + @Table + ' on'' 
and Data != ''Use Creditnet_Seed''

insert into @Temp values (''set identity_insert ' + @Table + ' off'')

insert into @Temp values (''GO'')

insert into @Temp values ('''')

Select * From @Temp

exec (@SQL)

-- Part 3 is to automate this on multiple tables with a query that -- loops the part 2 stored procedure

use DataStaging

if object_ID('{MySchema}.spTablesValidater') is not null
	drop proc {MySchema}.spTablesValidater

create proc {MySchema}.spTablesValidater
		@Type			varchar(16)
	,	@ResultType		varchar(16)


declare @Temp table
		TableID		int				identity(1,1)
	,	TableName	varchar(128)

	@Name		varchar(128)
,	@Start		int

insert into @Temp
select TableName
from {MySchema}.{TablesToRunTable}
where Keep_Remove = @Type

Set @Start = 1

while @Start <= (select count(*) from {MySchema}.{TablesToRunTable} where Keep_Remove = @Type)
	Select @Name =  TableName from @Temp where TableID = @Start
	if @ResultType = 'Script'
		exec {MySchema}.spScripter @Name
	set @Start = @Start + 1

-- 4th part is a table that is a listing of tables to go off of.
-- I choose to use the sql built in function 'sp_tables' after my
-- 'use {Database to choose from}.  Then I added a column for 
-- identification of what to move or not (Keep_remove) and added
-- another sproc to populate it's criteria.

Open in new window

LVL 75

Expert Comment

by:Anthony Perkins
ID: 33971631
>>I know Redgate probably has a publishing tool that puts this to shame.<<
That is correct.

Author Closing Comment

ID: 34021702
This was a difficult thing to ask for so I did not expect to get a complete answer but a list of possibilities which I did receive.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question