Re: how to find out if there is any primary key in each table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/07/04 

Date: Wed, 7 Jul 2004 12:59:20 -0400

This query will return only those tables that do not have a primary key.

SELECT t.TABLE_SCHEMA + '.' + t.TABLE_NAME
 FROM INFORMATION_SCHEMA.TABLES t
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
 ON c.TABLE_NAME = t.TABLE_NAME
 AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
 WHERE c.TABLE_NAME IS NULL
 AND OBJECTPROPERTY(OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME),
'IsMsShipped')=0 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"sms1" <sms1@pctc.com> wrote in message
news:2QVGc.31$KF.196@tor-nn1.netcom.ca...
> Gurus,
>
> I need to make sure each table (about 500) has one or more primary key. I
> plan to write an osql script.
>
> Question:
> What is the command / stored procedure that will tell such information in
> sql statement.
>
> Any help is much appreciated.
>
> Jenson
>
>

Relevant Pages

  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • Re: DISTINCTROW
    ... the sql statement is querying 2 tables and 1 stored query. ... but these are not unique indexes. ... tblB does not have primary key and does not have index. ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Create an MS Access Table in VB6
    ... You can run a simple DDL query to ... MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY, ... cn.Execute strsql ' the above sql statement ... create an AutoNumbering Index in my table as a Primary Key. ...
    (microsoft.public.vb.general.discussion)
  • referencing the outermost table from an inner view which is inside a subselect
    ... create table alm ( ... all_cod varchar2primary key, ... This mean that I have to write a SQL statement that cycles through all ... first query passing to it the values retrieved from all_cod_list instead ...
    (comp.databases.oracle.server)