Query for create table

Hai All,
I have table t1 in db1.
I want to create a table t1 in DB2 with the same structure as t1 of
db1.
Can anyone tell me the query?
Urgent... Looking forward for the response...Well you use QA, right click on the table t1 in db1 and choose option "scrip
t
object to new window.. and then change the database to execute the script.
But if you want a query, then try this.
use db2
select * into t1 from db1.dbo.t1
--replace the * with the column list
But in this case you wo't get the indexes and constraints. Just the skeleton
.
Hope this helps.|||There's a difference between these two techniques. The first (script db)
will give you just the structure, as the OP asked for, but the second will
copy all the DATA too. If that is what is wanted, great, but if not, you
might end up with a process that takes a long time, and a lot of logging
going on in DB2 and a lot of space being wasted.
To copy a table without copying the data, you need a select statement that
is guaranteed to return no rows, something like this:
use db2
select * into t1 from db1.dbo.t1
where 1 = 0
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
> Well you use QA, right click on the table t1 in db1 and choose option
> "script
> object to new window.. and then change the database to execute the script.
> But if you want a query, then try this.
> use db2
> select * into t1 from db1.dbo.t1
> --replace the * with the column list
> But in this case you wo't get the indexes and constraints. Just the
> skeleton.
> Hope this helps.
>|||Oops. I did intend to give the where clause 1=0. Dunno How I missed/messed i
t
:)
thanks for pointing it out.
--
"Kalen Delaney" wrote:

> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>
>|||Kalen
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
I think if the OP asked for table's structure the above technique is not
perfect because it does not move a Primary Keys as well as CONSTRAINS
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> There's a difference between these two techniques. The first (script db)
> will give you just the structure, as the OP asked for, but the second will
> copy all the DATA too. If that is what is wanted, great, but if not, you
> might end up with a process that takes a long time, and a lot of logging
> going on in DB2 and a lot of space being wasted.
> To copy a table without copying the data, you need a select statement that
> is guaranteed to return no rows, something like this:
> use db2
> select * into t1 from db1.dbo.t1
> where 1 = 0
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:78E120AB-5BC0-428C-9AC9-AC802A590532@.microsoft.com...
>|||Yes, I meant to just extend to what Omnibuzz said. He mentioned that this
solution wouldn't give you indexes and constraints, but I was only pointing
out that it WOULD give you the data, if you don't include the where clause
that is always false.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HuxIZxdGHA.4532@.TK2MSFTNGP02.phx.gbl...
> Kalen
> I think if the OP asked for table's structure the above technique is not
> perfect because it does not move a Primary Keys as well as CONSTRAINS
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23%23Qyo6pdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>