Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

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...
>

query for copying table

Hi,
I wanted to know a query in sql that would copy a whole table from one database to another database with both the structure and the content.
strangely, my college professor don't know the same.
I tried looking in the help menu in sql but it didn't helping me much.
Help me out...!! I have a deadline to meet. I really would appreciate it. thanxs. --vijay.You could use DTS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_71v7.asp), or the SELECT *
INTO newTable
FROM otherdatabase.owner.oldTablesyntax to copy your table from one database to another.

-PatP|||Books online is the first hand help you can get for any such syntax and information.|||Thank you very much. The query worked. Now i will get back to work and finish it off. I didn't understand about the DTS. but anyways the query was enuf.|||Refer to books online about DTS.

Query Assistance

Hi,
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>