Creating temporary table using a template table dynamically

I'm having an application for users to extract data from sql 2K. Problem is that I need to split the huge amount of data into few pages on the application screen.

Since my order by clause could be dynamic, which is a big problem, the solution I can think of is to populate a temporary table ( within a stored proc ) and assign a column within the temporary table with a running number.

This stored proc would accept among them, an argument, which is the table name of a base table.

However, I would like to create this stored proc in such a way that I could create a temporary table based on given based table name, complete with the field sizes and types. On top of that, would like to add a new column to this temp table to store a running number key.

Opinions appreciated. :)Refer to this linkSQL Performance (http://www.sql-server-performance.com/rd_temp_tables.asp) for information.

Creating Tables Dynamically

Hello!
I am looking for a simple routine to create and populate tables dynamically.
I get an error message that says @.tn has a problem. What could be the reason
:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
set @.table_name = 'test'
set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
Any clues are very welcome. Thank you very much!
-BahmanYou need to declare @.tn.
Archer
"Bahman" wrote:

> Hello!
> I am looking for a simple routine to create and populate tables dynamicall
y.
> I get an error message that says @.tn has a problem. What could be the reas
on:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
> set @.table_name = 'test'
> set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
> execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
> Any clues are very welcome. Thank you very much!
> -Bahman
>|||@.tn is redundant. You can't parameterize a table name, even within
sp_executesql. Try:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.table_name = 'test'
set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
nvarchar)'
execute sp_executesql @.query
sp_executesql is redundant as well. You could have used EXEC.
The obvious question is why would you want to create tables
dynamically? Especially since the metadata is static in this example.
David Portas
SQL Server MVP
--|||Bagman3rd:
No, it still won't work. Does sp_executesql look for parameters only in the
where clause?
"bagman3rd" wrote:
> You need to declare @.tn.
> Archer
> "Bahman" wrote:
>|||>> I am looking for a simple routine to create and populate tables
Can you elaborate on why you want to do this? In most cases, such
requirements stem from poorly designed systems or people trying to come up
with short-cuts to avoid coding.
The solution for creating a table dynamically is to build a string the exact
CREATE TABLE script and execute it with EXEC or sp_ExecuteSQL. With
sp_ExecuteSQL you can only substitute variables not literal segments of a
DDL statement, which you seem to be doing here. So one way to write this
would be:
SET @.table_name = 'test'
SET @.parmdefinition = '( tn varchar(500),query varchar(500) )'
SET @.query = 'create table ' + @.table_name + @.parmdefinition
EXEC sp_executesql @.query
Before starting out, consider reading some implications at:
http://www.sommarskog.se/dynamic_sql.html
Anith|||Dave,
You are correct.
What I am looking for is the exact image of one particular table every hour
or so.
I am not looking for transaction logs, or sql statements, or monitor
traffic. All I want to know is what exactly is the content of the table at a
given time.
In MSSql you can backup the database or the trx log. But you cnnot create
images of individual tables that you can revert to on a moment's notice.
In this particular table, users can both add and delete records. Obviously,
they can only delete their own records. But to be safe, I need an image of
the content of the table itself, since add/delete permissions are not
'layered', e.g., once you have the right to 'delete', that right in not
strictly associated with what you entered yourself. That right is also
associated with what everyone else entered. Same with update.
There are other issues here as well. But the above are the basic stuff.
Thank you very much for your help.
-Bahman
"David Portas" wrote:

> @.tn is redundant. You can't parameterize a table name, even within
> sp_executesql. Try:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.table_name = 'test'
> set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
> nvarchar)'
> execute sp_executesql @.query
> sp_executesql is redundant as well. You could have used EXEC.
> The obvious question is why would you want to create tables
> dynamically? Especially since the metadata is static in this example.
> --
> David Portas
> SQL Server MVP
> --
>|||That's certainly an original one! Seriously, the problem is a common
one and there are various standard solutions. You don't need to copy
tables around dynamically. In fact it's hard to imagine how such a
thing could be practical let alone scalable.
Take a look at the following article on row-level security. More simply
you could just implement "logical" deletes - i.e. use a proc that marks
rows as deleted rather than actually deleting them.
http://vyaskn.tripod.com/ row_level...as
es.htm
David Portas
SQL Server MVP
--|||David,
That solution is pretty much what I am already doing. It is not robust thoug
h.
As you said, when you 'scale up' the database, you cannot create a separate
account for everyone who logs in to your website. So, the same account is
adding and deleting stuff from the same table all over the place. The
'session' variable that you have in the web application is not linked to the
'session' that you have on the Sequel. The two exist in two separate domains
.
Hence, the sequel does not know 'who' is 'on' right now judging from the
calling application, which is dont net.
That is just what our code is for. But some of it should be off-loaded to
the sequel side. The calling application should not be in charge of
everything.
In Windows 2003 you have a feature called 'shadow copy'. That is not the
same as tape backup. To me, it is a form of versioning. Good enough. In
sequel, you have the trx that you can apply after you recreated the database
from the backup. But there is nothing in the trx or the backup that tells yo
u
what the difference is between the state of an individual table that you see
there and the one you backed up earlier (unless you like lots of
programming). There is a 'disconnect' here.
MS Sql is basically treated as a data dump. There are cases where it has
very limited functionality. A simple trigger that I put in increases respons
e
time by a second. The server is not designed to be 'active'. And there is no
fine granule for treating data and data relationships. Hence the need to cod
e
a lot.
Thank you!
"David Portas" wrote:

> That's certainly an original one! Seriously, the problem is a common
> one and there are various standard solutions. You don't need to copy
> tables around dynamically. In fact it's hard to imagine how such a
> thing could be practical let alone scalable.
> Take a look at the following article on row-level security. More simply
> you could just implement "logical" deletes - i.e. use a proc that marks
> rows as deleted rather than actually deleting them.
> http://vyaskn.tripod.com/ row_level...as
es.htm
> --
> David Portas
> SQL Server MVP
> --
>|||>> What I am looking for is the exact image of one particular table every ho
ur or so. <<
I know of one ACCESS system that crashes so often, they do this. It
says that you need a better solution. You will have to do this in
hardware to get the exact image on a second identical disk drive.
Weird request -- most of us are happy with snapshots or generatianl
concurrency control.

creating table fields dynamically from another table row

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100)... )

Table: Dummy
================================================== ==
field1 field2 field3........
Empid Empname empaage1 sam 23.........
2 rai 22.........
.
.
.
n raj 45..........
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int...)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage.........
1 sam 23............
2 rai 22............
.
.
.
n raj 45.............
================================================== ==

I want to do this dynamically....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated...

Waiting for replies......
sabyyou can do something like below. but determining the exact filed type in CREATE table is difficult and avoiding that I have taken all fields as varchar(100)

create table #T (SQLTxt varchar(5000))

declare @.SQL varchar(5000)
declare @.FinalSQL varchar(5000)
declare @.ii int

set @.SQL=''
set @.ii=1
while @.ii <= 140
begin
set @.SQL = @.SQL + ' Field' + cast(@.ii as varchar) + ' + '' varchar(100) null,''+'
set @.ii=@.ii+1
end
set @.SQL = left(@.SQL,len(@.SQL)-3)+''''
set @.SQL = 'insert into #T (SQLTxt) select top 1 ' + '''create table Dummy2 (''+' + @.SQL + '+'')''' + ' from Dummy'

exec (@.SQL)
set @.FinalSQL = (select top 1 SQLTxt from #T)
exec (@.FinalSQL)
drop table #T

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

Creating Dynamic Variables

Is there any way I can create variables dynamically in SQL server procedures?

e.g., I initially declare variable
@.col1='1'
@.col2='0'

and in a for loop with variable @.i,

I have @.str='@.col'+@.i
if @.i=1 then the @.str='@.col1'
and try to insert the value this variable holds into a table as

insert into table1 values(@.str)

Here I want to insert the value of @.col1(which is 1) and NOT '@.col1'

I tried so many time unsuccessfully.Has anyone done this kind of stuff before?any work arounds for this?

appreciate u'r help..

Thanks!Chances are you can accomplish your objectives using standard SQL, with maybe a little help from temporary tables, table variables, or case statements. I don't understand what you are trying to do, but your code looks like SQL written by a VB programmer (loops, inserting parameters, etc...). These are legitimate tools for SQL, but most procedures don't require them.

Using a dynamic SQL statement may be an option, either to assign the value to @.str or for the insert statement, but dynamic SQL is tricky because it executes in a distinct environment and your variables go out of scope.

If you could briefly describe your application, relevant table structures, and the task you are trying to perform, I may be able to give your some programming algorythms that are more appropriate for SQL.

blindman

Creating Dynamic Database using Script

I am looking for a way to dynamically create a database in SQL Server
2000 using a stored procedure that will create a dynamically named new
database using the script from another database.
More specifically, I would like to call a stored procedure that will
pass in a new DB name and will take the script that I have created from
another database and simply create a new database with the new name
using the old scripts table structure, etc. It might take reading in
the script and passing in a dynamic variable, but I am having trouble
with it, as everytime I try to create a stored procedure with the
database script in it and substitute the variable @.NEWDBNAME in the
place of each instance of the database name in the script, I get errors
when trying to save the SP.
Any help with this would be hugely appreciated.Hi
Posting your code would have been useful!
You will need to make the whole statement "dynamic" and then execute the
statement.
CREATE PROCEDURE spr_create_new_database
AS
BEGIN
DECLARE @.dbname sysname
DECLARE @.stmt varchar(4000)
/* Create New ISD Backup database dynamically */
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
','')
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
If you are going to create this database regularly then you may want to
create a template database and copy the file and use sp_attach_db to create a
nwe database, alternatively you could use backup/restore. If you want to
create a copy of your live database using RESTORE may be a quicker option
that creating the tables and pulling the data across.
You could use a DTS package to populate the database with a dynamic
properties task and a Copy SQL Server Objects task. This would allow you to
set the destination database without having to change the package.
John
"SQL Server Group 1" wrote:
> I am looking for a way to dynamically create a database in SQL Server
> 2000 using a stored procedure that will create a dynamically named new
> database using the script from another database.
> More specifically, I would like to call a stored procedure that will
> pass in a new DB name and will take the script that I have created from
> another database and simply create a new database with the new name
> using the old scripts table structure, etc. It might take reading in
> the script and passing in a dynamic variable, but I am having trouble
> with it, as everytime I try to create a stored procedure with the
> database script in it and substitute the variable @.NEWDBNAME in the
> place of each instance of the database name in the script, I get errors
> when trying to save the SP.
> Any help with this would be hugely appreciated.
>|||Thanks John for the reply. I am now in the middle of this and need
just a little clarification.
What I did to this point is alter the script to create a database named
"Connect_Template". Then I created the database from the query
analyzer from within the master database and saved the DTS package that
did that. Now I have a database called "Connect_Templage" and a DTS
package called "Create New DB From Termplate".
What I need to do from here is to create a SP that will create a new
database from that DTS package with a new name being passed in. Is
there a way to do that?
In my case, every time a new customer gets created, a new database
based on that template structure needs to be created and I can then
dynamically add a DSN to it from my end. I just need to figure out
what methodis best and will work.
The script to create my database is really really long, but I wonder if
the sp_detach_db and sp_attach_db would work passing in dynamic names?
Thanks again for your help...you have given me a lot of options. I just
need one that would easily work.
I am trying your dynamic statements now but am wondering if the DTP
method would work passing in a dynamic variable.
John Bell wrote:
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to create a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you to
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
> > I am looking for a way to dynamically create a database in SQL Server
> > 2000 using a stored procedure that will create a dynamically named new
> > database using the script from another database.
> >
> > More specifically, I would like to call a stored procedure that will
> > pass in a new DB name and will take the script that I have created from
> > another database and simply create a new database with the new name
> > using the old scripts table structure, etc. It might take reading in
> > the script and passing in a dynamic variable, but I am having trouble
> > with it, as everytime I try to create a stored procedure with the
> > database script in it and substitute the variable @.NEWDBNAME in the
> > place of each instance of the database name in the script, I get errors
> > when trying to save the SP.
> >
> > Any help with this would be hugely appreciated.
> >
> >|||Just for a try, I ran the code below from the Master Database, and it
compiled and worked.
I think that might be the best way to run that. The only thing is now,
how would be the best way to run the create table structures, SPs, etc.
once the new database gets created? How would I create the DTS package
with a dynamic properties task? I have used DTS to import and export
from specific places but not anything dynamically.
Thanks again...this is really helping!
John Bell wrote:
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to create a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you to
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
> > I am looking for a way to dynamically create a database in SQL Server
> > 2000 using a stored procedure that will create a dynamically named new
> > database using the script from another database.
> >
> > More specifically, I would like to call a stored procedure that will
> > pass in a new DB name and will take the script that I have created from
> > another database and simply create a new database with the new name
> > using the old scripts table structure, etc. It might take reading in
> > the script and passing in a dynamic variable, but I am having trouble
> > with it, as everytime I try to create a stored procedure with the
> > database script in it and substitute the variable @.NEWDBNAME in the
> > place of each instance of the database name in the script, I get errors
> > when trying to save the SP.
> >
> > Any help with this would be hugely appreciated.
> >
> >|||Hi
The procedure is fine if you want to create a database with a given name,
but to use that else where you should really create the name and pass it as a
parameter to the stored procedure. You can then call a DTS package and pass
the database name as a global variable using xp_cmdshell to call DTSRUN see
http://www.sqldts.com/default.aspx?301 about getting help on the command
syntax.
Using this global variable in the DTS package, it can be assigned using the
dynamic properties task can assign it to the destination of a Copy SQL Server
objects task.
e.g.
CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
Within the job step you can then do something like:
DECLARE @.cmd varchar(4000)
DECLARE @.dbname sysname
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
EXEC spr_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
This will run the dts package called "DTS Package" on the current server
using trusted authentication, and pass the global variable @.dbname which has
just been used to create the backup database. The global variable is called
"Backup Database Name" You could even pass the source database name as a
global variable and also assign that in the dynamic properties task to make
it totally flexible.
Before you create the DTS package run the stored procedure first so that a
destination database exists for the Copy SQL Objects task. When you create
the DTS Package, create Copy SQL Objects task first and test it, before
creating the global variables and then finally create the Dynamic Properties
task and the workflow to make sure that the Dynamic Properties task runs
before the Copy SQL Objects task.
HTH
John
"SQL Server Group 1" wrote:
> Just for a try, I ran the code below from the Master Database, and it
> compiled and worked.
> I think that might be the best way to run that. The only thing is now,
> how would be the best way to run the create table structures, SPs, etc.
> once the new database gets created? How would I create the DTS package
> with a dynamic properties task? I have used DTS to import and export
> from specific places but not anything dynamically.
> Thanks again...this is really helping!
>
> John Bell wrote:
> > Hi
> >
> > Posting your code would have been useful!
> >
> > You will need to make the whole statement "dynamic" and then execute the
> > statement.
> >
> > CREATE PROCEDURE spr_create_new_database
> > AS
> > BEGIN
> > DECLARE @.dbname sysname
> > DECLARE @.stmt varchar(4000)
> >
> > /* Create New ISD Backup database dynamically */
> > SET @.dbname = 'BackupDB_' +
> > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > ','')
> >
> > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> >
> > EXEC ( @.stmt )
> >
> > RETURN
> > END
> >
> > If you are going to create this database regularly then you may want to
> > create a template database and copy the file and use sp_attach_db to create a
> > nwe database, alternatively you could use backup/restore. If you want to
> > create a copy of your live database using RESTORE may be a quicker option
> > that creating the tables and pulling the data across.
> >
> > You could use a DTS package to populate the database with a dynamic
> > properties task and a Copy SQL Server Objects task. This would allow you to
> > set the destination database without having to change the package.
> >
> > John
> >
> > "SQL Server Group 1" wrote:
> >
> > > I am looking for a way to dynamically create a database in SQL Server
> > > 2000 using a stored procedure that will create a dynamically named new
> > > database using the script from another database.
> > >
> > > More specifically, I would like to call a stored procedure that will
> > > pass in a new DB name and will take the script that I have created from
> > > another database and simply create a new database with the new name
> > > using the old scripts table structure, etc. It might take reading in
> > > the script and passing in a dynamic variable, but I am having trouble
> > > with it, as everytime I try to create a stored procedure with the
> > > database script in it and substitute the variable @.NEWDBNAME in the
> > > place of each instance of the database name in the script, I get errors
> > > when trying to save the SP.
> > >
> > > Any help with this would be hugely appreciated.
> > >
> > >
>|||I really appreciate the help...I am sort of new to the advanced DTS
stuff but have used DTS for import, export. So I will give this a try
and see if I can get it.
Thanks for the link. In creating the Advanced DTS run object, what
would be the dynamic variable name for the database name that is passed
to the copy object? Would it be in the Properties List in the Dynamic
Properties task component under Tasks > Copy SQL Server Objects? For
example: DestinationDatabase, DestinationLogin, DestinationPassword?
If that's right, I think I can do this...
John Bell wrote:
> Hi
> The procedure is fine if you want to create a database with a given name,
> but to use that else where you should really create the name and pass it as a
> parameter to the stored procedure. You can then call a DTS package and pass
> the database name as a global variable using xp_cmdshell to call DTSRUN see
> http://www.sqldts.com/default.aspx?301 about getting help on the command
> syntax.
> Using this global variable in the DTS package, it can be assigned using the
> dynamic properties task can assign it to the destination of a Copy SQL Server
> objects task.
> e.g.
> CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> Within the job step you can then do something like:
> DECLARE @.cmd varchar(4000)
> DECLARE @.dbname sysname
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
> EXEC spr_create_new_database @.dbname
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
> SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
> EXEC master..xp_cmdshell @.cmd
> This will run the dts package called "DTS Package" on the current server
> using trusted authentication, and pass the global variable @.dbname which has
> just been used to create the backup database. The global variable is called
> "Backup Database Name" You could even pass the source database name as a
> global variable and also assign that in the dynamic properties task to make
> it totally flexible.
> Before you create the DTS package run the stored procedure first so that a
> destination database exists for the Copy SQL Objects task. When you create
> the DTS Package, create Copy SQL Objects task first and test it, before
> creating the global variables and then finally create the Dynamic Properties
> task and the workflow to make sure that the Dynamic Properties task runs
> before the Copy SQL Objects task.
> HTH
> John
> "SQL Server Group 1" wrote:
> > Just for a try, I ran the code below from the Master Database, and it
> > compiled and worked.
> >
> > I think that might be the best way to run that. The only thing is now,
> > how would be the best way to run the create table structures, SPs, etc.
> > once the new database gets created? How would I create the DTS package
> > with a dynamic properties task? I have used DTS to import and export
> > from specific places but not anything dynamically.
> >
> > Thanks again...this is really helping!
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Posting your code would have been useful!
> > >
> > > You will need to make the whole statement "dynamic" and then execute the
> > > statement.
> > >
> > > CREATE PROCEDURE spr_create_new_database
> > > AS
> > > BEGIN
> > > DECLARE @.dbname sysname
> > > DECLARE @.stmt varchar(4000)
> > >
> > > /* Create New ISD Backup database dynamically */
> > > SET @.dbname = 'BackupDB_' +
> > > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > > ','')
> > >
> > > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > >
> > > EXEC ( @.stmt )
> > >
> > > RETURN
> > > END
> > >
> > > If you are going to create this database regularly then you may want to
> > > create a template database and copy the file and use sp_attach_db to create a
> > > nwe database, alternatively you could use backup/restore. If you want to
> > > create a copy of your live database using RESTORE may be a quicker option
> > > that creating the tables and pulling the data across.
> > >
> > > You could use a DTS package to populate the database with a dynamic
> > > properties task and a Copy SQL Server Objects task. This would allow you to
> > > set the destination database without having to change the package.
> > >
> > > John
> > >
> > > "SQL Server Group 1" wrote:
> > >
> > > > I am looking for a way to dynamically create a database in SQL Server
> > > > 2000 using a stored procedure that will create a dynamically named new
> > > > database using the script from another database.
> > > >
> > > > More specifically, I would like to call a stored procedure that will
> > > > pass in a new DB name and will take the script that I have created from
> > > > another database and simply create a new database with the new name
> > > > using the old scripts table structure, etc. It might take reading in
> > > > the script and passing in a dynamic variable, but I am having trouble
> > > > with it, as everytime I try to create a stored procedure with the
> > > > database script in it and substitute the variable @.NEWDBNAME in the
> > > > place of each instance of the database name in the script, I get errors
> > > > when trying to save the SP.
> > > >
> > > > Any help with this would be hugely appreciated.
> > > >
> > > >
> >
> >|||Hi
If you only have the two tasks then the will be called something like
DTSTask_DTSTransferObjectsTask_1
and DTSTask_DTSDynamicPropertiesTask_1
These will be listed under Tasks in the left hand pane of the "Dynamic
Properties Task: Package Properties" dialogue. Choose
DTSTask_DTSTransferObjectsTask_1.
On the right hand side a table of Property Names and Default Values will
appear, double click on DestinationDatabase in the Property Name column and
you will get the Add/Edit Assignment dialogue. Choose Global Variables as the
source and your Global variable will be in the name of the drop down.
HTH
John
"SQL Server Group 1" wrote:
> I really appreciate the help...I am sort of new to the advanced DTS
> stuff but have used DTS for import, export. So I will give this a try
> and see if I can get it.
> Thanks for the link. In creating the Advanced DTS run object, what
> would be the dynamic variable name for the database name that is passed
> to the copy object? Would it be in the Properties List in the Dynamic
> Properties task component under Tasks > Copy SQL Server Objects? For
> example: DestinationDatabase, DestinationLogin, DestinationPassword?
> If that's right, I think I can do this...
>
> John Bell wrote:
> > Hi
> >
> > The procedure is fine if you want to create a database with a given name,
> > but to use that else where you should really create the name and pass it as a
> > parameter to the stored procedure. You can then call a DTS package and pass
> > the database name as a global variable using xp_cmdshell to call DTSRUN see
> > http://www.sqldts.com/default.aspx?301 about getting help on the command
> > syntax.
> > Using this global variable in the DTS package, it can be assigned using the
> > dynamic properties task can assign it to the destination of a Copy SQL Server
> > objects task.
> >
> > e.g.
> >
> > CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
> > AS
> > BEGIN
> > DECLARE @.stmt varchar(4000)
> >
> > /* Create New Backup database dynamically */
> > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > EXEC ( @.stmt )
> > RETURN
> > END
> >
> > Within the job step you can then do something like:
> >
> > DECLARE @.cmd varchar(4000)
> > DECLARE @.dbname sysname
> >
> > SET @.dbname = 'BackupDB_' +
> > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
> >
> > EXEC spr_create_new_database @.dbname
> >
> > SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
> > SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
> >
> > EXEC master..xp_cmdshell @.cmd
> >
> > This will run the dts package called "DTS Package" on the current server
> > using trusted authentication, and pass the global variable @.dbname which has
> > just been used to create the backup database. The global variable is called
> > "Backup Database Name" You could even pass the source database name as a
> > global variable and also assign that in the dynamic properties task to make
> > it totally flexible.
> >
> > Before you create the DTS package run the stored procedure first so that a
> > destination database exists for the Copy SQL Objects task. When you create
> > the DTS Package, create Copy SQL Objects task first and test it, before
> > creating the global variables and then finally create the Dynamic Properties
> > task and the workflow to make sure that the Dynamic Properties task runs
> > before the Copy SQL Objects task.
> >
> > HTH
> >
> > John
> > "SQL Server Group 1" wrote:
> >
> > > Just for a try, I ran the code below from the Master Database, and it
> > > compiled and worked.
> > >
> > > I think that might be the best way to run that. The only thing is now,
> > > how would be the best way to run the create table structures, SPs, etc.
> > > once the new database gets created? How would I create the DTS package
> > > with a dynamic properties task? I have used DTS to import and export
> > > from specific places but not anything dynamically.
> > >
> > > Thanks again...this is really helping!
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Posting your code would have been useful!
> > > >
> > > > You will need to make the whole statement "dynamic" and then execute the
> > > > statement.
> > > >
> > > > CREATE PROCEDURE spr_create_new_database
> > > > AS
> > > > BEGIN
> > > > DECLARE @.dbname sysname
> > > > DECLARE @.stmt varchar(4000)
> > > >
> > > > /* Create New ISD Backup database dynamically */
> > > > SET @.dbname = 'BackupDB_' +
> > > > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > > > ','')
> > > >
> > > > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > > >
> > > > EXEC ( @.stmt )
> > > >
> > > > RETURN
> > > > END
> > > >
> > > > If you are going to create this database regularly then you may want to
> > > > create a template database and copy the file and use sp_attach_db to create a
> > > > nwe database, alternatively you could use backup/restore. If you want to
> > > > create a copy of your live database using RESTORE may be a quicker option
> > > > that creating the tables and pulling the data across.
> > > >
> > > > You could use a DTS package to populate the database with a dynamic
> > > > properties task and a Copy SQL Server Objects task. This would allow you to
> > > > set the destination database without having to change the package.
> > > >
> > > > John
> > > >
> > > > "SQL Server Group 1" wrote:
> > > >
> > > > > I am looking for a way to dynamically create a database in SQL Server
> > > > > 2000 using a stored procedure that will create a dynamically named new
> > > > > database using the script from another database.
> > > > >
> > > > > More specifically, I would like to call a stored procedure that will
> > > > > pass in a new DB name and will take the script that I have created from
> > > > > another database and simply create a new database with the new name
> > > > > using the old scripts table structure, etc. It might take reading in
> > > > > the script and passing in a dynamic variable, but I am having trouble
> > > > > with it, as everytime I try to create a stored procedure with the
> > > > > database script in it and substitute the variable @.NEWDBNAME in the
> > > > > place of each instance of the database name in the script, I get errors
> > > > > when trying to save the SP.
> > > > >
> > > > > Any help with this would be hugely appreciated.
> > > > >
> > > > >
> > >
> > >
>|||John,
I almost have it, I think. Here is the stored procedure code I am
running. It created the new database just fine, but it doesn't copy
the data from the template database.
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
GO
----.
The exec code is:
exec sprk_create_new_database @.dbname = 'TestCopy'
The resulting text is:
The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'TestCopy_log'.
However, when it gets to the copy DTS package, "Create_New_Connect_DB",
it does not pass in the variable 'dbname' to the package that has a
Dynamic Properties Variable that passes that name into the
'DestinationDatabase' variable of the Copy SQL Server Objects Task.
Can you see anything here that looks wrong? Again, I really appreciate
the help. I think I'm really close to solving this one!
Brandon|||Hi
You are passing the name to the stored procedure rather than setting the
local variable with the name. The code will not work at all without the local
variable being declared!. This may be easier if you create a procedure to
both call your database creation procedure and the DTS Package (This may wrap)
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
Then run sprk_create_new_database_and_populate_it passing the name of the
database you wish to populate:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
HTH
John
"SQL Server Group 1" wrote:
> John,
> I almost have it, I think. Here is the stored procedure code I am
> running. It created the new database just fine, but it doesn't copy
> the data from the template database.
> CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
>
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
>
> DECLARE @.cmd varchar(4000)
> EXEC sprk_create_new_database @.dbname
>
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
> "Create_New_Connect_DB" '
> SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
>
> EXEC master..xp_cmdshell @.cmd
> GO
> ----.
> The exec code is:
> exec sprk_create_new_database @.dbname = 'TestCopy'
> The resulting text is:
> The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'TestCopy_log'.
> However, when it gets to the copy DTS package, "Create_New_Connect_DB",
> it does not pass in the variable 'dbname' to the package that has a
> Dynamic Properties Variable that passes that name into the
> 'DestinationDatabase' variable of the Copy SQL Server Objects Task.
> Can you see anything here that looks wrong? Again, I really appreciate
> the help. I think I'm really close to solving this one!
> Brandon
>|||OK, here are the two SPs
1) sprk_create_new_database_and_populate_it
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
2) sprk_create_new_database
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
GO
When I run:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
I get the message:
DTSRun: Must specify a value for switch: /N The parameter is
incorrect.
DTSRun: Invalid command options
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):
Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also
specified)
/~R Repository Database Name <uses default if blank; loads package
from repository database>
Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string
(including name:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>
DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from
Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y
and /!X)>
Notes:
~ is optional; if present, the parameter is hex text of encrypted
value (0x313233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except
multiple /A)
Error: -2147024809 (80070057); Provider Error: 0 (0)
Error string: DTSRun: Must specify a value for switch: /N The
parameter is incorrect.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 0
NULL
Any thoughts...I does created the Database first, which is hopeful.
It just doesn't get through the population part.
Thanks!!|||Hi
Instead of "EXEC master..xp_cmdshell @.cmd" use SELECT @.cmd to print it out.
You could then see the whole command, and if necessary run it from a command
prompt. Check Create_New_Connect_DB is the package name (check no trailing
spaces) and you have saved it to the server.
You may want to compare what you are running against the output from
http://www.sqldts.com/default.aspx?301
John
"SQL Server Group 1" wrote:
> OK, here are the two SPs
> 1) sprk_create_new_database_and_populate_it
>
> CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
> sysname )
> AS
> BEGIN
> DECLARE @.cmd varchar(4000)
>
> EXEC sprk_create_new_database @.dbname
>
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
> "Create_New_Connect_DB" '
> SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
> EXEC master..xp_cmdshell @.cmd
> END
> GO
> 2) sprk_create_new_database
> CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
>
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> GO
>
> When I run:
> EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
> I get the message:
> DTSRun: Must specify a value for switch: /N The parameter is
> incorrect.
> DTSRun: Invalid command options
> Usage: dtsrun /option [value] [/option [value]] ...
> Options ('/?' shows this screen; '-' May be substituted for '/'):
> Package retrieval:
> /~S Server Name
> /~U User Name
> /~P Password
> /E <Use trusted connection instead of /U /P>
> /~N Package Name
> /~M Package Password
> /~G Package Guid String
> /~V Package Version Guid String
> /~F Structured Storage UNC filename (overwritten if /S also
> specified)
> /~R Repository Database Name <uses default if blank; loads package
> from repository database>
> Package operation (overrides stored Package settings):
> /~A Global Variable Name:typeid=Value <may quote entire string
> (including name:typeid)>
> /~L Log file name
> /~W Write Completion Status to Windows Event Log <True or False>
> DTSRun action (default is to execute Package):
> /!X <Do not execute; retrieves Package to /F filename>
> /!D <Do not execute; drop package from SQL Server (cannot drop from
> Storage File)>
> /!Y <Do not execute; output encrypted command line>
> /!C <Copies command line to Windows clipboard (may be used with /!Y
> and /!X)>
> Notes:
> ~ is optional; if present, the parameter is hex text of encrypted
> value (0x313233...)
> Whitespace between command switch and value is optional
> Embedded whitespace in values must be embedded in double-quotes
> If an option is specified multiple times, the last one wins (except
> multiple /A)
>
> Error: -2147024809 (80070057); Provider Error: 0 (0)
> Error string: DTSRun: Must specify a value for switch: /N The
> parameter is incorrect.
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts80.hlp
> Help context: 0
> NULL
> Any thoughts...I does created the Database first, which is hopeful.
> It just doesn't get through the population part.
> Thanks!!
>|||OK..Last try.
I now have everything working if I pass in a preset variable, but I
can't seem to get the formatting right when I try to pass in the the
dynamic variable name "@.dbname"
Here is my query analyzer statement:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'NewConnectDB'
Here is what works:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A
"DestDatabase":"8"="NewConnectDB" /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
Notice above that I made the DestDatabase a constant and not passing in
@.dbname. That works using the exec statement at the top.
Here is what does not work:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A "DestDatabase":"8"="'+@.dbname+'" /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
GO
This gives me an error:
Error string: [SQL-DMO]The name ' "NewConnectDB"' was not found in
the Databases collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
I can't seem to format the @.dbname in the cmd statement that will pass
in the value NewConnectDB that is formatted properly. Can you see what
the problem might be?
Thanks again, as I have been working on this for quite a while and
think I am almost done.
Brandon
GO|||AWESOME!!!!
I figured it out finally.
Here is the DTS run string that works just for future reference:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A "DestDatabase":"8"=' +
@.dbname + ' /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
GO
I really, really apprciate your help and thank you for your patience.
I should have a good foundation on which to base my future dynamic DTS
packages!
Brandon

Creating Dynamic Database using Script

I am looking for a way to dynamically create a database in SQL Server
2000 using a stored procedure that will create a dynamically named new
database using the script from another database.
More specifically, I would like to call a stored procedure that will
pass in a new DB name and will take the script that I have created from
another database and simply create a new database with the new name
using the old scripts table structure, etc. It might take reading in
the script and passing in a dynamic variable, but I am having trouble
with it, as everytime I try to create a stored procedure with the
database script in it and substitute the variable @.NEWDBNAME in the
place of each instance of the database name in the script, I get errors
when trying to save the SP.
Any help with this would be hugely appreciated.Hi
Posting your code would have been useful!
You will need to make the whole statement "dynamic" and then execute the
statement.
CREATE PROCEDURE spr_create_new_database
AS
BEGIN
DECLARE @.dbname sysname
DECLARE @.stmt varchar(4000)
/* Create New ISD Backup database dynamically */
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16)
,GETDATE(),121),':',''),'-',''),'
','')
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
If you are going to create this database regularly then you may want to
create a template database and copy the file and use sp_attach_db to create
a
nwe database, alternatively you could use backup/restore. If you want to
create a copy of your live database using RESTORE may be a quicker option
that creating the tables and pulling the data across.
You could use a DTS package to populate the database with a dynamic
properties task and a Copy SQL Server Objects task. This would allow you to
set the destination database without having to change the package.
John
"SQL Server Group 1" wrote:

> I am looking for a way to dynamically create a database in SQL Server
> 2000 using a stored procedure that will create a dynamically named new
> database using the script from another database.
> More specifically, I would like to call a stored procedure that will
> pass in a new DB name and will take the script that I have created from
> another database and simply create a new database with the new name
> using the old scripts table structure, etc. It might take reading in
> the script and passing in a dynamic variable, but I am having trouble
> with it, as everytime I try to create a stored procedure with the
> database script in it and substitute the variable @.NEWDBNAME in the
> place of each instance of the database name in the script, I get errors
> when trying to save the SP.
> Any help with this would be hugely appreciated.
>|||Thanks John for the reply. I am now in the middle of this and need
just a little clarification.
What I did to this point is alter the script to create a database named
"Connect_Template". Then I created the database from the query
analyzer from within the master database and saved the DTS package that
did that. Now I have a database called "Connect_Templage" and a DTS
package called "Create New DB From Termplate".
What I need to do from here is to create a SP that will create a new
database from that DTS package with a new name being passed in. Is
there a way to do that?
In my case, every time a new customer gets created, a new database
based on that template structure needs to be created and I can then
dynamically add a DSN to it from my end. I just need to figure out
what methodis best and will work.
The script to create my database is really really long, but I wonder if
the sp_detach_db and sp_attach_db would work passing in dynamic names?
Thanks again for your help...you have given me a lot of options. I just
need one that would easily work.
I am trying your dynamic statements now but am wondering if the DTP
method would work passing in a dynamic variable.
John Bell wrote:[vbcol=seagreen]
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16)
,GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to creat
e a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you t
o
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
>|||Just for a try, I ran the code below from the Master Database, and it
compiled and worked.
I think that might be the best way to run that. The only thing is now,
how would be the best way to run the create table structures, SPs, etc.
once the new database gets created? How would I create the DTS package
with a dynamic properties task? I have used DTS to import and export
from specific places but not anything dynamically.
Thanks again...this is really helping!
John Bell wrote:[vbcol=seagreen]
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16)
,GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to creat
e a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you t
o
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
>|||Hi
The procedure is fine if you want to create a database with a given name,
but to use that else where you should really create the name and pass it as
a
parameter to the stored procedure. You can then call a DTS package and pass
the database name as a global variable using xp_cmdshell to call DTSRUN see
http://www.sqldts.com/default.aspx?301 about getting help on the command
syntax.
Using this global variable in the DTS package, it can be assigned using the
dynamic properties task can assign it to the destination of a Copy SQL Serve
r
objects task.
e.g.
CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
Within the job step you can then do something like:
DECLARE @.cmd varchar(4000)
DECLARE @.dbname sysname
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16)
,GETDATE(),121),':',''),'-',''),'','
')
EXEC spr_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
This will run the dts package called "DTS Package" on the current server
using trusted authentication, and pass the global variable @.dbname which has
just been used to create the backup database. The global variable is called
"Backup Database Name" You could even pass the source database name as a
global variable and also assign that in the dynamic properties task to make
it totally flexible.
Before you create the DTS package run the stored procedure first so that a
destination database exists for the Copy SQL Objects task. When you create
the DTS Package, create Copy SQL Objects task first and test it, before
creating the global variables and then finally create the Dynamic Properties
task and the workflow to make sure that the Dynamic Properties task runs
before the Copy SQL Objects task.
HTH
John
"SQL Server Group 1" wrote:

> Just for a try, I ran the code below from the Master Database, and it
> compiled and worked.
> I think that might be the best way to run that. The only thing is now,
> how would be the best way to run the create table structures, SPs, etc.
> once the new database gets created? How would I create the DTS package
> with a dynamic properties task? I have used DTS to import and export
> from specific places but not anything dynamically.
> Thanks again...this is really helping!
>
> John Bell wrote:
>|||I really appreciate the help...I am sort of new to the advanced DTS
stuff but have used DTS for import, export. So I will give this a try
and see if I can get it.
Thanks for the link. In creating the Advanced DTS run object, what
would be the dynamic variable name for the database name that is passed
to the copy object? Would it be in the Properties List in the Dynamic
Properties task component under Tasks > Copy SQL Server Objects? For
example: DestinationDatabase, DestinationLogin, DestinationPassword?
If that's right, I think I can do this...
John Bell wrote:[vbcol=seagreen]
> Hi
> The procedure is fine if you want to create a database with a given name,
> but to use that else where you should really create the name and pass it a
s a
> parameter to the stored procedure. You can then call a DTS package and pas
s
> the database name as a global variable using xp_cmdshell to call DTSRUN se
e
> http://www.sqldts.com/default.aspx?301 about getting help on the command
> syntax.
> Using this global variable in the DTS package, it can be assigned using th
e
> dynamic properties task can assign it to the destination of a Copy SQL Ser
ver
> objects task.
> e.g.
> CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> Within the job step you can then do something like:
> DECLARE @.cmd varchar(4000)
> DECLARE @.dbname sysname
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16)
,GETDATE(),121),':',''),'-',''),''
,'')
> EXEC spr_create_new_database @.dbname
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
> SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
> EXEC master..xp_cmdshell @.cmd
> This will run the dts package called "DTS Package" on the current server
> using trusted authentication, and pass the global variable @.dbname which h
as
> just been used to create the backup database. The global variable is calle
d
> "Backup Database Name" You could even pass the source database name as a
> global variable and also assign that in the dynamic properties task to mak
e
> it totally flexible.
> Before you create the DTS package run the stored procedure first so that a
> destination database exists for the Copy SQL Objects task. When you create
> the DTS Package, create Copy SQL Objects task first and test it, before
> creating the global variables and then finally create the Dynamic Properti
es
> task and the workflow to make sure that the Dynamic Properties task runs
> before the Copy SQL Objects task.
> HTH
> John
> "SQL Server Group 1" wrote:
>|||Hi
If you only have the two tasks then the will be called something like
DTSTask_DTSTransferObjectsTask_1
and DTSTask_DTSDynamicPropertiesTask_1
These will be listed under Tasks in the left hand pane of the "Dynamic
Properties Task: Package Properties" dialogue. Choose
DTSTask_DTSTransferObjectsTask_1.
On the right hand side a table of Property Names and Default Values will
appear, double click on DestinationDatabase in the Property Name column and
you will get the Add/Edit Assignment dialogue. Choose Global Variables as th
e
source and your Global variable will be in the name of the drop down.
HTH
John
"SQL Server Group 1" wrote:

> I really appreciate the help...I am sort of new to the advanced DTS
> stuff but have used DTS for import, export. So I will give this a try
> and see if I can get it.
> Thanks for the link. In creating the Advanced DTS run object, what
> would be the dynamic variable name for the database name that is passed
> to the copy object? Would it be in the Properties List in the Dynamic
> Properties task component under Tasks > Copy SQL Server Objects? For
> example: DestinationDatabase, DestinationLogin, DestinationPassword?
> If that's right, I think I can do this...
>
> John Bell wrote:
>|||John,
I almost have it, I think. Here is the stored procedure code I am
running. It created the new database just fine, but it doesn't copy
the data from the template database.
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
GO
----.
The exec code is:
exec sprk_create_new_database @.dbname = 'TestCopy'
The resulting text is:
The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'TestCopy_log'.
However, when it gets to the copy DTS package, "Create_New_Connect_DB",
it does not pass in the variable 'dbname' to the package that has a
Dynamic Properties Variable that passes that name into the
'DestinationDatabase' variable of the Copy SQL Server Objects Task.
Can you see anything here that looks wrong? Again, I really appreciate
the help. I think I'm really close to solving this one!
Brandon|||Hi
You are passing the name to the stored procedure rather than setting the
local variable with the name. The code will not work at all without the loca
l
variable being declared!. This may be easier if you create a procedure to
both call your database creation procedure and the DTS Package (This may wra
p)
CREATE PROCEDURE sprk_create_new_database_and_populate_it
( @.dbname sysname
)
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
Then run sprk_create_new_database_and_populate_it
passing the name of the
database you wish to populate:
EXEC sprk_create_new_database_and_populate_it
@.dbname = 'TestCopy'
HTH
John
"SQL Server Group 1" wrote:

> John,
> I almost have it, I think. Here is the stored procedure code I am
> running. It created the new database just fine, but it doesn't copy
> the data from the template database.
> CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
>
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
>
> DECLARE @.cmd varchar(4000)
> EXEC sprk_create_new_database @.dbname
>
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
> "Create_New_Connect_DB" '
> SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
>
> EXEC master..xp_cmdshell @.cmd
> GO
> ----.
> The exec code is:
> exec sprk_create_new_database @.dbname = 'TestCopy'
> The resulting text is:
> The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'TestCopy_log'.
> However, when it gets to the copy DTS package, "Create_New_Connect_DB",
> it does not pass in the variable 'dbname' to the package that has a
> Dynamic Properties Variable that passes that name into the
> 'DestinationDatabase' variable of the Copy SQL Server Objects Task.
> Can you see anything here that looks wrong? Again, I really appreciate
> the help. I think I'm really close to solving this one!
> Brandon
>|||OK, here are the two SPs
1) sprk_create_new_database_and_populate_it
CREATE PROCEDURE sprk_create_new_database_and_populate_it
( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
2) sprk_create_new_database
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
GO
When I run:
EXEC sprk_create_new_database_and_populate_it
@.dbname = 'TestCopy'
I get the message:
DTSRun: Must specify a value for switch: /N The parameter is
incorrect.
DTSRun: Invalid command options
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):
Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also
specified)
/~R Repository Database Name <uses default if blank; loads package
from repository database>
Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string
(including name:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>
DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from
Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y
and /!X)>
Notes:
~ is optional; if present, the parameter is hex text of encrypted
value (0x313233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except
multiple /A)
Error: -2147024809 (80070057); Provider Error: 0 (0)
Error string: DTSRun: Must specify a value for switch: /N The
parameter is incorrect.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 0
NULL
Any thoughts...I does created the Database first, which is hopeful.
It just doesn't get through the population part.
Thanks!!

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...

Creating Duplicate Table

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

It's amazing what a little searching can find.

Check out this thread from just yesterday. It tells you all you need to know.

|||

I have created one, but not tested 100%.. try this code..

Code Snippet

Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid

Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder

Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;

Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;

--Print @.SQL

Exec(@.SQL);
Print 'Table Structered Copied'

If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End

Go

Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;

Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End

return @.IndexedCols;
End

Go

exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0

|||

Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...

|||

I fixed the bugs.. check now

|||

Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.

|||

Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...

|||Which db you are using SQL Server 2000/2005?

|||Hi Manivannan..we are using Sql server 2000.|||

I fixed the bug again.. .

Can you check now?

|||

Hi Manivannan... Now i am able to Create. Thank u Very Much...