Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

Finding Books for SQL SERVER Replication

Hi,
I am new in this dbforums. i working on SQL SERVER REPLICATION. I need some books related to only SQL SERVER REPLICATION. If any body konwn then reply meHave you alredy checked out BOL, 'cause there is lots of information to be found here!sql

find out all indexes for a table

Does the command "sp_helpindex table_name" finds out all
indexes related to this table? Thanks.
Yes.
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.
|||In a nutshell,
Yes
Rick Sawtell
MCT, MCSD, MCDBA
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.

find out all indexes for a table

Does the command "sp_helpindex table_name" finds out all
indexes related to this table? Thanks.Yes.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.|||In a nutshell,
Yes
Rick Sawtell
MCT, MCSD, MCDBA
"Bill" <fei0405@.yahoo.com> wrote in message
news:275801c49cf5$565702c0$a401280a@.phx.gbl...
> Does the command "sp_helpindex table_name" finds out all
> indexes related to this table? Thanks.

find all tables that a table is related to

how can i find all the names of tables that a specific table is related to in tsql?

sp_depends

Displays information about database object dependencies (for example, the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure). References to objects outside the current database are not reported.

Examples

This example lists the database objects that depend on the Customers table.

USE Northwind

EXEC sp_depends 'Customers'

|||

Assuming you have referential integrity in place you can also query the sys.sysforeignkeys view in 2005 or the sysforeignkeys table in 2000. Here is a query returning all table associations (2005 syntax, to use in 2000 remove "sys." prefix):

SELECT

o.name [parentTable]

,o2.name [childTable]

FROM

sys.sysobjects o

INNER JOIN sys.sysforeignkeys fk ON fk.rkeyid = o.id

INNER JOIN sys.sysobjects o2 ON o2.id = fk.fkeyid AND o2.id <> o.id

WHERE

o.name = 'myTable'