getting a list of user created tables ONLY

sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>

GetDate

I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

Get uniqueness of a column from the system tables or information_schema

I'm trying to write a query which, from a given table name, will
produce a list of column names with an indicator as to whether it is
unique. By unique, I mean it a) is the column in a single-column
primary key, b) is the column in a single-column unique constraint, or
c) is the column in single-column unique index.
So, for this DDL,
-- CODE BEGINS
create table t1 (
c1 int not null primary key,
c2 int not null unique,
c3 int not null,
c4 int not null
)
create unique index ix1 on t1 (c3)
-- drop table t1
-- CODE ENDS
I'd like a query that will produce something like this output
c1 yes
c2 yes
c3 yes
c4 no
I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
information_schema, but I'm getting nowhere. Anyone have any hints?
Thomas BergI forgot to say: I'm using SQL Server 2000 SP4.|||Hello, Thomas
This query returns the desired result:
SELECT name,
CASE WHEN EXISTS (
SELECT * FROM sysindexkeys k
INNER JOIN sysindexes i
ON k.id=i.id AND k.indid=i.indid
WHERE k.id=c.id AND k.colid=c.colid
AND INDEXPROPERTY(i.id,i.name,'IsUnique')=1
AND NOT EXISTS (
SELECT * FROM sysindexkeys k2
WHERE k.id=k2.id AND k.indid=k2.indid
AND k.keyno<>k2.keyno
)
) THEN 'yes' ELSE 'no' END AS IsUnique
FROM syscolumns c WHERE id=OBJECT_ID('t1')
Note that it's sufficient to search only for unique indexes, because
primary keys and unique keys are always enforced by creating a unique
index with the same name on the specified columns.
For a more thorough testing of the query, I added the following:
create unique index ix2 on t1 (c4,c3)
create index ix3 on t1 (c4)
Razvan|||tbergNoSpamPlease@.insight-system.co.jp a crit :
> I'm trying to write a query which, from a given table name, will
> produce a list of column names with an indicator as to whether it is
> unique. By unique, I mean it a) is the column in a single-column
> primary key, b) is the column in a single-column unique constraint, or
> c) is the column in single-column unique index.
> So, for this DDL,
> -- CODE BEGINS
> create table t1 (
> c1 int not null primary key,
> c2 int not null unique,
> c3 int not null,
> c4 int not null
> )
> create unique index ix1 on t1 (c3)
> -- drop table t1
> -- CODE ENDS
> I'd like a query that will produce something like this output
> c1 yes
> c2 yes
> c3 yes
> c4 no
> I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
> information_schema, but I'm getting nowhere. Anyone have any hints?
> Thomas Berg
>
Here is a very general query wich give you all informations about
indexes with columns and uniqueness
SELECT
u.name AS IXD_SCHEMA_NAME,
o.name AS IXD_TABLE_NAME,
i.name AS IXD_INDEX_NAME,
CONSTRAINT_TYPE AS IXD_CONSTRAINT_TYPE,
CASE
WHEN i.indid = 0 THEN 'TABLE'
WHEN i.indid = 1 THEN 'CLUSTER'
WHEN i.indid BETWEEN 2 AND 254 THEN 'HEAP'
WHEN i.indid = 255 THEN 'TXTEIMAGE'
END AS IXD_INDEX_TYPE,
INDEXPROPERTY(o.id, i.name, 'IsUnique') AS IXD_IS_UNIQUE,
INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') AS IXD_FILL_FACTOR,
c.name AS IXD_COL_NAME,
DATA_TYPE + '('+
CAST(COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS
VARCHAR(16))
+ COALESCE(', '+CAST(NULLIF(NUMERIC_SCALE, 0) AS
VARCHAR(16)) , '') +')' AS IXD_COL_TYPE,
k.keyno AS IXD_COL_IDX_ORDER,
CASE
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 0 THEN 'ASC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 1 THEN 'DESC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) IS NULL THEN ''
END AS IXD_COL_DATA_ORDER,
INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed') AS
IXD_ROW_LOCK_DISALLOWED,
INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed') AS
IXD_PAGE_LOCK_DISALLOWED
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
INNER JOIN dbo.sysindexkeys k
ON o.id = k.id
and i.indid = k.indid
INNER JOIN dbo.syscolumns c
ON k.colid = c.colid
and o.id = c.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON u.name = ISC.TABLE_SCHEMA
AND o.name = ISC.TABLE_NAME
AND c.name = ISC.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON u.name = TCT.CONSTRAINT_SCHEMA
AND i.name = TCT.CONSTRAINT_NAME
WHERE i.status & 64 <> 64 -- sauf les index "stat"
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||You guys are brilliant. Thanks.

get the rows where the info from one table is not contained in the

hello,
i have 2 tables which have 2 fields.
Common in the 2 tables is the id, the other field is a varchar(256)
example
Table1
Id UserInfo
1 Pc A.Julien-3400
2 Soft V.Noris-2800
3 Liz Barbara -2345
Table2
Id Username
1 Julien
2 Jack
3 Barbara
I want to get the id value where the username is not contained in the UserIn
fo
In the example
for id=1 Julien is contained in Pc A.Julien-3400
for id=2 Jack !!! is not contained .....
for id=3 Barbara is contained in Liz Barbara -2345
For this case i want to get only id=2
thanks
best regardsYou can do something like:
SELECT
<your column list>
FROM
table1
JOIN table2 ON table1.id = table2.id AND CHARINDEX(table2.col,
table1.col) > 0
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:1DB6FF37-9D4A-4A9A-A486-6E0C382F0070@.microsoft.com...
> hello,
> i have 2 tables which have 2 fields.
> Common in the 2 tables is the id, the other field is a varchar(256)
> example
> Table1
> Id UserInfo
> 1 Pc A.Julien-3400
> 2 Soft V.Noris-2800
> 3 Liz Barbara -2345
> Table2
> Id Username
> 1 Julien
> 2 Jack
> 3 Barbara
> I want to get the id value where the username is not contained in the
> UserInfo
> In the example
> for id=1 Julien is contained in Pc A.Julien-3400
> for id=2 Jack !!! is not contained .....
> for id=3 Barbara is contained in Liz Barbara -2345
>
> For this case i want to get only id=2
> thanks
> best regards|||On Wed, 1 Feb 2006 06:52:27 -0800, Xavier wrote:

>hello,
>i have 2 tables which have 2 fields.
>Common in the 2 tables is the id, the other field is a varchar(256)
>example
>Table1
>Id UserInfo
>1 Pc A.Julien-3400
>2 Soft V.Noris-2800
>3 Liz Barbara -2345
>Table2
>Id Username
>1 Julien
>2 Jack
>3 Barbara
>I want to get the id value where the username is not contained in the UserI
nfo
>In the example
>for id=1 Julien is contained in Pc A.Julien-3400
>for id=2 Jack !!! is not contained .....
>for id=3 Barbara is contained in Liz Barbara -2345
>
>For this case i want to get only id=2
Hi Xavier,
SELECT Table1.Id, Table1.UserInfo, Table2.UserName
FROM Table1
INNER JOIN Table2
ON Table2.Id = Table1.Id
WHERE Table1.UserInfo NOT LIKE '%' + Table2.Username + '%'
Hugo Kornelis, SQL Server MVP

Get the recent records

i have a datetime field in the post tables.

I would like to get the records within the latest 7 days.

Are there any functions for doing something like this?

my current query is something like

select * from post where creation_time ...

Thank you

try something like this:

create

table #test(datedatetime)

insert

into #test

values

('01/01/2007')

insert

into #test

values

('02/01/2007')

insert

into #test

values

('02/04/2007')

select

*from #test

where

date>dateadd(day,-7,getdate())

drop

table #test

I think that it will point you in correct direction, or maybe it is your solution?

|||

Could you not just do...

select

*from post

where

creation_time >dateadd(day,-7,getdate())

jpazgier, i am not following the reason for creating the additional table.

|||

I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.

But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.

Thanks

|||Both answers are great!|||

Both answers are great!

Thank you

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.
How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>
|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type = "'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =3D 'BASE TABLE'
-- Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
> > CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type ==3D
> "'Table'"
> > it won't do it as it can only work in its own context. I have also =tried
> using the use command with a database name as a parameter to point it =at the
> database. It won't let me do that either. Any ideas, Regards.
> >|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:
>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at th
e
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
--
David Portas
SQL Server MVP
--

Get the description of INFORMATION_SCHEMA.COLUMNS

I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
--

Get tablename in trigger

Hello,
I have a general trigger program used for many tables, but how do I refer to
the tablename currently been modified in my script?
Thanks!
PerCREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
> Hello,
> I have a general trigger program used for many tables, but how do I refer
> to
> the tablename currently been modified in my script?
> Thanks!
> Per
>|||Working perfect :-)
Thanks!
"Uri Dimant" wrote:

> CREATE TABLE TT
> (
> COL INT
> )
> CREATE TRIGGER MY_TR ON TT
> FOR INSERT
> AS
> DECLARE @.ObjID int
> SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
> SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
> INSERT INTO TT VALUES (1)
> SELECT * FROM TT
> DROP TABLE TT
>
>
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
>
>|||I'm not certain what you mean by a "general trigger program". If you
mean you are generating and re-using the same code for each table then
surely you would put the table name in there when you generate the
code, in which case there would be no need to do it dynamically at
runtime. That's the method I would recommend anyway.
If you mean you are calling the same proc from each trigger then Uri's
code won't help you. In that case I think you will have to pass the
table name as a parameter.
David Portas
SQL Server MVP
--|||The code was OK, I am writting one procedure which can apply to many tables,
however I have a issue with dynamic SQL, which I have put in a new post.
Per
"David Portas" wrote:

> I'm not certain what you mean by a "general trigger program". If you
> mean you are generating and re-using the same code for each table then
> surely you would put the table name in there when you generate the
> code, in which case there would be no need to do it dynamically at
> runtime. That's the method I would recommend anyway.
> If you mean you are calling the same proc from each trigger then Uri's
> code won't help you. In that case I think you will have to pass the
> table name as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>

Get table_names and column_names

I want to create a function(procedure) which find all my table_names and (column names in those tables) in my database. Do anyone know of a existing function that retrives these names?You can use the INFORMATION_SCHEMA.Columns view for this information.

SELECT * FROM INFORMATION_SCHEMA.Columns

There is also a view called Tables which might be of interest to you.

Terri

get table name

Hi

I need a sql command that will give me the tables in a database.

Thanks in advance.

Cemal

Try this:

SELECT [name]FROMSYSOBJECTSWHERE xtype ='U'

|||

Or this:

SELECT TABLE_NAMEFROMINFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='dbo'AND TABLE_TYPE='BASE TABLE'

|||

here is the error message I get when I try to run my code which you will see here.

Server Error in '/' Application.

---

The Microsoft Jet database engine cannot open the file 'INFORMATION_SCHEMA'. It is already opened exclusively by another user, or you need permission to view its data.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'INFORMATION_SCHEMA'. It is already opened exclusively by another user, or you need permission to view its data.

Source Error:

Line 41: ' Execute the command returning a Data Reader Line 42: Dim reader As OleDbDataReaderLine 43: reader = cmd.ExecuteReader()Line 44: ' Enuermate all the rows of data in the sheet Line 45: Dim i As Integer = 0

'------------------

' Excel File:

'

Dim sFileAsString =Me.RadioButtonList1.SelectedValue

Dim conAsNew OleDbConnection

Dim cmdAs OleDbCommand

' Create an OLEDB connection to the Excel Workbook

'I am trying both below script and get same error.

' con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

con.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile +";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

' Open the connection

con.Open()

' Create a Command object to get all data from the Worksheet

cmd =New OleDbCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='dbo' AND TABLE_TYPE ='BASE TABLE'", con)

' Execute the command returning a Data Reader

Dim readerAs OleDbDataReader

reader = cmd.ExecuteReader()

' Enuermate all the rows of data in the sheet

Dim iAsInteger = 0

While reader.Read()

' Display the contents of the first column

For i = 0To reader.FieldCount - 1

Response.Write(reader.GetString(i))

Next

EndWhile

' Close the reader

reader.Close()

' Close the connection

con.Close()

|||

Have you tried the solution I posted above?

|||

Patron, are you using a Microsoft Access database? If so, try:

select name from msysobjects where type = 1 and lvprop <> null;

|||

yes I tried yours, I got same problem which says table (known as the excel sheet) is not accessable becuase it is open by another user. etc.

I checked my web hosting account and made sure, I had read and write access to the file .

Can you provide anymore help on this?

Thanks in advance,.

CEMAL

|||

nanotasher:

Microsoft Access database

Ah good catch.

|||

no my codes and site is on a remote hosting company.

and I am trying to access to an excel file. Just want to read its contents with oledbreader

|||

Are you trying to read the names of which worksheets are contained within an Excel workbook? Or trying to read the contents of a single worksheet?

|||

yes,. First I want to get the worksheet name (each file will have only one worksheet) then I am gonna use it to access it's contents.

Please let me know if I am doing wrong. With this script my main goal is to read each excel file and import them into sql server table. (after check if exist any of the records)

anyway, please let me know what you think.

thanks

Cemal

|||

First, you have to have MDAC installed. I'm going to assume you have done this already.

Second, you would get at the contents by using some code like this:

using System.Data.OleDb;
using System.Data;

privatevoid Populate()
{
if (txtFile.Text.Length > 0)
{
try
{
string connectionString =@."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + txtFile.Text +";Extended Properties= \"Excel 8.0;HDR=YES;\"";

OleDbConnection connection=newOleDbConnection(connectionString);
connection.Open();

// connection.GetSchema("Tables") will return a datatable chock full of the names of each worksheet.. Get the TABLE_NAME column
OleDbCommand command=newOleDbCommand("SELECT * FROM [" + connection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString() +]", connection);
OleDbDataAdapter oda=newOleDbDataAdapter();

oda.SelectCommand = command;
oda.Fill(ds);

connection.Close();

grdResult.DataSource = ds;
}
catch
{
Clear();
}
}
}

privatevoid Clear()
{
grdResult.DataSource =null;
}

This was a small example I wrote in a test harness application. You'll likely need to modify it to get it to work with what you're doing. The point is, you can read the results directly into a dataset pretty easily.

|||

Hi Guys,

First of all I want to say, I appriciate all the help you are trying to provide.

During all of my tries I wasn't able to make the script work for one or another reason. First of all I tried, sql commands that didn't work (neither one of them)

later on I found this getschema object of vb.net and started practicing on it. I pasted the code below.

Dim slocAsString = Request.MapPath("importfiles")

Dim strConn2AsString ="Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" + sloc +"\" +Me.RadioButtonList1.SelectedValue & _

";Extended Properties=""Excel 8.0;"""

'Response.Write(strConn2)

Dim restrictions(3)AsString

vn.ConnectionString = strConn2

vn.Open()

Dim tableAs DataTable = vn.GetSchema("Tables", restrictions)

Response.Write(table.Columns.Item(2).DefaultValue) ' THIS LINE GAVE ME NO INFORMATION AT ALL. BECUASE I DON'T KNOW HOW TO GET A HOLD OF THE OBJECT.

GridView1.DataSource = table

GridView1.DataBind()

table.Dispose()

vn.Close()

TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_GUIDDESCRIPTIONTABLE_PROPIDDATE_CREATEDDATE_MODIFIED 'bagscarrycasesA$'TABLE 8/9/2007 11:10:21 AM8/9/2007 11:10:21 AM

this is the result I get I after I run the script.

Now, I only need help getting the table_name value where it shows 'bagscarrycasesA$'

Thanks in advance.

|||

Never Mind guys.

I change ;

Response.Write(table.Rows(0).Item("TABLE_NAME"))

and it worked . Now I can get the excel sheet name.

thanks for all your help.

|||

Hey man thanks for that help.

Get table date using sysobjects and syscolumns. Quick?

All my online research has told me how to get info about a table field's data using the system tables, but I can't find anything that tells how to get that table field's data specifically. Could be that it's difficult to explain so difficult to search on but I know there has to be a way to do this and it can't be that difficult.

How do I use the system tables sysobjects and syscolumns to give me the data from a specific field in a third table?

Basically I don't want to know the field type for a tables field, I want to know that field's value.

Let's say I have a table called tblCompanies and that table has 4 fields. idCompany, companyName, companyState, and companyCountry.

How can I return the value as a command parameter for any one of the 4 fields using sysobjects and syscolumns?

If I were writing dynamic SQL I would do something like this:

set @.valueToReturn = exec ('select ' + @.fieldNameToReturn + ' from ' + @.tableToSearch + ' where ' + @.fieldToMatchOn + ' = ' + @.valueToMatchOn)

But I don't want to use dynamic SQL, I want to use the existing system tables to write a straightforward query. My nonfunctioning/English version of this would be:

give me the value for the field name I send in as a string (@.fieldNameToReturn)
from the table I send in as a string (@.tblToSearch)
where (sysobjects.name = @.tblToSearch) and (syscolumns.name = @.fieldNameToReturn) and (@.tblToSearch.@.fieldnameToMatchOn = @.valueToMatchAgainst)

I'm using sysobjects and syscolumns because that's where I can use my variables for table name and column name to link. I just can't figure out how to get hold of my actual data table and the values in it.

Does that make any sense to anyone? I'm sure someone has had to want something like this.

Thank you, thank you, thank you!you can't do this kind of thing unless you use dynamic sql.

http://www.sommarskog.se/dynamic_sql.html

get Stored Procedures Parameters, how?

hi
i make smal Application to get information from SQL Server 2000 by using
VB6.
now i can get Databases, Tables and Colomn, and Stored Procedures, but my
problem how i can get SP Parametres?
i am thinking to make small function to get Parametres From SP.Text, but i
think its not good solution.
Tarek M. SialaMaybe you could cross-post to a few more groups, or try this search engine
called google before casting such a wide net. Anyway, here is one page that
might help. Followups set accordingly.
http://www.aspfaq.com/2463
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:edmJJ3scGHA.3908@.TK2MSFTNGP04.phx.gbl...
> hi
> i make smal Application to get information from SQL Server 2000 by using
> VB6.
> now i can get Databases, Tables and Colomn, and Stored Procedures, but my
> problem how i can get SP Parametres?
> i am thinking to make small function to get Parametres From SP.Text, but i
> think its not good solution.
> --
> Tarek M. Siala
>

Get statistics on db objects

I'm using something like:

SELECT COUNT(*) 'Number of System Tables' FROM dbo.sysobjects

WHERE xtype = 's'

to get statistics on database objects but I seem to remember that theres a more efficient way. For example, is there some way to add something like a where clause to the first table returned by sp_help (where Object_type = 'view', for example)?

Thanks,

Dave

SP_TABLES can be used in this csae with parameters for '

Table, system table, or view.'

Get Results into one table

What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
ThanksTry:
select
*
from
Table1
union all
select
*
from
Table2 t2
where not exists
(
select
*
from
Table2 t2
where
t2.[ID] = t1.[ID]
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
What is the easiest way to do the follwoing.
I have 2 tables. I want all the rows from one table and what is left from
the second table.
I.e. table 1.
ID value
1 10
2 20
3 30
i.e. table 2
ID value
1 15
2 25
3 35
4 444
i want my final table to have the following (everything from table 1 and
left over from table 2)
ID value
1 10
2 20
3 30
4 4444
Thanks|||Here's another solution:
select table2.ID,
ISNULL(table1.value, table2.value) AS value
from table2
left outer join table1 on table2.ID = table1.ID
"Fab" <lazzaro@.rogers.com> wrote in message
news:ew4q9HKPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks|||Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||On Tue, 28 Feb 2006 15:02:56 -0500, Fab wrote:

>What is the easiest way to do the follwoing.
>I have 2 tables. I want all the rows from one table and what is left from
>the second table.
>I.e. table 1.
>ID value
>1 10
>2 20
>3 30
>i.e. table 2
>ID value
>1 15
>2 25
>3 35
>4 444
>i want my final table to have the following (everything from table 1 and
>left over from table 2)
>ID value
>1 10
>2 20
>3 30
>4 4444
>Thanks
>
Hi Fab,
SELECT t2.ID, COALESCE(t1.value, t2.value)
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1
ON t1.ID = t2.ID
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Hugo Kornelis, SQL Server MVP|||Another way is to use "full join".
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
> Hi Tom.
> Me thinks there's a problem with your query.
> This should work.
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t1
> where not exists
> (
> select
> *
> from
> Table1 t2
> where
> t2.[ID] = t1.[ID]
> )
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||Ah, yes. Good catch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eJPO4mKPGHA.420@.tk2msftngp13.phx.gbl...
Hi Tom.
Me thinks there's a problem with your query.
This should work.
select
*
from
Table1
union all
select
*
from
Table2 t1
where not exists
(
select
*
from
Table1 t2
where
t2.[ID] = t1.[ID]
)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uueE$JKPGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> *
> from
> Table1
> union all
> select
> *
> from
> Table2 t2
> where not exists
> (
> select
> *
> from
> Table2 t2
> where
> t2.[ID] = t1.[ID]
> )
> --
> Tom|||--@.@.@. TESTED and works
Create Table #tbl1
([ID] int,
[Value]varchar(10))
INSERT #tbl1 ([ID],[VALUE])
VALUES (1,'10')
INSERT #tbl1 ([ID],[VALUE])
VALUES (2,'20')
INSERT #tbl1 ([ID],[VALUE])
VALUES (3,'30')
Create Table #tbl2
([ID] int,
[Value]varchar(10))
INSERT #tbl2 ([ID],[VALUE])
VALUES (1,'15')
INSERT #tbl2 ([ID],[VALUE])
VALUES (2,'25')
INSERT #tbl2 ([ID],[VALUE])
VALUES (3,'35')
INSERT #tbl2 ([ID],[VALUE])
VALUES (4,'444')
-- View both
SELECT *
FROM #tbl1
SELECT *
FROM #tbl2
-- Combine to make all display
SELECT *
FROM #tbl1
UNION ALL
SELECT *
from #tbl2
where not exists(select *
from #tbl1
where [ID] = #tbl2.[ID])
--@.@.@. TESTED and works
"Fab" wrote:

> What is the easiest way to do the follwoing.
> I have 2 tables. I want all the rows from one table and what is left from
> the second table.
> I.e. table 1.
> ID value
> 1 10
> 2 20
> 3 30
> i.e. table 2
> ID value
> 1 15
> 2 25
> 3 35
> 4 444
> i want my final table to have the following (everything from table 1 and
> left over from table 2)
> ID value
> 1 10
> 2 20
> 3 30
> 4 4444
> Thanks
>
>|||Thanks to everyone for your help...
The solution Joseph provided worked.
:-)
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:E841A384-DEE2-4CC7-A939-A5F6C13DD975@.microsoft.com...
> --@.@.@. TESTED and works
> Create Table #tbl1
> ([ID] int,
> [Value]varchar(10))
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (1,'10')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (2,'20')
>
> INSERT #tbl1 ([ID],[VALUE])
> VALUES (3,'30')
>
>
> Create Table #tbl2
> ([ID] int,
> [Value]varchar(10))
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (1,'15')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (2,'25')
>
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (3,'35')
> INSERT #tbl2 ([ID],[VALUE])
> VALUES (4,'444')
>
> -- View both
> SELECT *
> FROM #tbl1
> SELECT *
> FROM #tbl2
>
> -- Combine to make all display
> SELECT *
> FROM #tbl1
> UNION ALL
> SELECT *
> from #tbl2
> where not exists(select *
> from #tbl1
> where [ID] = #tbl2.[ID])
>
>
> --@.@.@. TESTED and works
> "Fab" wrote:
>